Database joined element dropdown returning to Please Select when opening record for editing

[Cre]

For some reason I can select from the dropdown fine when I create a record but if I need to edit it then the field defaults back to 'Please Select'.

I tried deselecting the Please Select option but all it did was default to the first of the dropdown list.

A different field with a similar setup seems to work fine.

Curious.

 

[Cre]

This is turning into a real head scratcher.

The site is a Field Naturalists web page. Members can make observations the forms use a dropdown list from a table join to a species list. It works fine for the birds but with the butterflies all is good until someone goes to edit their observation record. Then the butterfly species dropdown reverts to "Please Select". The birds do not exhibit this behaviour.

In comparing them both I can not see any differences between the two elements and both forms show identical options and do both menu items. I'm obviously missing something but am struggling to think where else I should look. Any help would be appreciated.

 

[cheesegrits]

Do you have a 'where' filter on the butterfly join element?

-- hugh

 

[Cre]

Do you have a 'where' filter on the butterfly join element?

-- hugh

Hi Hugh,
Thanks for the post.
There is no 'where' filter (and i'm assuming you mean data-where) on either the butterflies or the birds.
Cheers

 

[troester]

Is the value shown correctly in list view? Can you check directly in your DB if it's stored in the DB table?
What did you select as "Value" in Data settings?

 

[Cre]

Hi troester,

I have chosen butterfly_common_name for the value and also for label. The table it is from is butterfly_species_list which only contains that element plus an id element.

The value is showing fine in list view and also presents fine in the csv.

Thanks

 

[Cre]

Okay I bit the bullet and replicated the butterfly species list with slightly different element name, simply redirected the join in the observation form and now it works.

For the life of me I can not see any difference between the two, just some wee glitch somewhere probably introduced by me but all solved now hopefully. Thanks for the suggestion though.

 

[cheesegrits]

You really shouldn't use the name as the value. Use the id (primary key field).

Sent from my HTC One using Tapatalk

 

[Cre]

Hi Hugh,

That is what I did originally but then the column in list view just showed zeros rather than names. This is despite the 'label' field remaining butterfly_common_name. When I then edited a record the dropdown contained the names but after saving it presents as an id number in the list so I have switched it back.

Very tempted to let sleeping dogs lie unless it was obvious to you what might be giving it a brain fade.

 

[cheesegrits]

It's worth sorting this out now, before your app is in production (assuming you are still building it). Not using the PK as the FK ('value') for a join can cause all kinds of nasty problems in the future, when you try and add functionality.

Try creating another test join to the butterfly table, using the id as the value. Make sure you haven't unpublished the 'id' element on that table, and that it is an auto-incrementing primary key for the table.

If that doesn't work properly, let me know and I'll take a look. I don't usually do hands on support in Community, but I'd rather take a few mins and get you on the right track now, than have to help you re-build your data in a few months when things go pear shaped.

One small example of why you shouldn't use "names" as the key. Imagine you made a typo in the name, and didn't notice until you had a bunch of data collected. So you change the name on the butterfly record to fix it. Now all your records which have joins pointing to that no longer work, because the name no longer matches. So you'd have to manually fix all the now incorrect data. But PK's never change, so you can change the "label" all you want, and it won't affect the database relationships.

There are also some places in Fabrik, in some of the more advanced features, where we make assumptions about the Fk -> PK relationship, like in "related data".

-- hugh

 

[Cre]

Hi Hugh,

Thank you very much for the post and the offer to assist if we can't fix this. The club's webmaster and I are going to gird the loins later this week and have a crack at sorting it out the way you have recommended. Fingers crossed. This site was my first real play at Fabrik so there are bound to be some little time bombs. The webmaster has been keen to get his hands dirty so he can develop the site further therefore it will be a good exercise for him too.

Cheers

Cre

 

[Cre]

Hi Hugh,

The webmaster has returned after a moth collecting expedition in desert country and we had a chance to sit down and do some testing. You had suggested;

"Try creating another test join to the butterfly table, using the id as the value. Make sure you haven't unpublished the 'id' element on that table, and that it is an auto-incrementing primary key for the table."
​

I'm afraid the same issue has arisen. When we go back in to edit a record the dropdown returns to the default value.

I have check the id is published, is the primary key, and is auto-incrementing. We have attempted a few variations all to no avail. I'm therefore hoping we might be able to take you up on the very kind offer to have a look at the site. I have left the test field in place at the bottom of the butterfly observations form viewed only by administrators.

Cheers

Cre