- The data can be copied to a field in the table "Bathces" or just shown/linked, whatever is possible or the easiest to achieve (I do not want to make things over complicated at this stage).
- If data in the fields "common name" and "latin name" in the layout "Batches" gets changed,
If the user should be able to change that data, but such changes must not be reflected at the source, that doesn't leave you a choice as per the method in 3., because these two options don't work simultaneously with a reference. So you need to copy over the texts from Flowers.
One way that also lets you know what flower the batch is based on:
• create a new value list, “using field data” from Flowers; 1. field: primary key, 2. field: common name, show only second field
• in Batches, create a new field id_flower (same data type as the primary key in the Flowers table). This is a “foreign” (not alien) key. (* see note below)
• create a relationship Flowers::primaryKey = Batches::id_flower
• put the new field on the layout, format it as a popup with the new value list.
• Set the auto-enter option for Batches::common name and Batches::latin name to simply use the corresponding field from Flowers.
If you can ensure that e.g. the Latin name is unique, and you don't care about keeping a connection to the Flowers source record, you could use the same method, but a bit simpler:
• create a new value list, “using field data” from Flowers; 1. (and only) field: Latin name
• create a relationship Flowers::Latin Name = Batches::Latin Name
• put Latin name on the layout, format it as a popup with the new value list.
• Set the auto-enter option for Batches::Common name to simply use the corresponding field from Flowers.
* What is the foreign key in Flowers doing? Your structure implies “one flower, many batches”, which means Flowers is the parent to Batches, and consequently Batches needs a flower foreign key (as discussed above). But there isn't a parent to Flowers, so that table doesn't need any foreign key.
Also note that since (almost) every table should have (exactly) one primary key, it is sufficient to speak of “the” primary key – but when you talk about foreign keys, you need to specify which related table it is for.
I would recommend creating a third table called Batch_Flowers that would be used to stored the flowers needed for a Batch. I think this is like an invoice where Batches is the invoice, Batch_flowers would be the line items on the invoice and Flowers would be the available products that I can choose from for the line items.
Thank you for your detailed description. Part one worked partially, but not all the way unfortunately. When I change the drop down field "common name" and select a flower, the list of flowers is displayed alright (it was´t before), but when I select a flower the flower_id is shown instead of the common name. Also the other field "latin name" is not changed at all, but the data from the old flower is shown. When I enter the table with the Flowers (source table), I also see that the common name is replaced with the Flower_ID instead of the name. Not so good. I have double checked your steps and I do not think that I have missed anything. Please see attachments of screenshots. By the way, a "batch" can only contain ONE (1) Flower. If they start several batches the same day, they must enter a record for each batch/flower. The reason is that this data base also will contain information about what has been done with the different batches, such as the use of chemicals, fertilizers etc., and that varies from flower to flower.
Using the latin name as a relation could be an option, but the thing is that many flowers are the same type but they are of different subspecies (ssp), for instance a Christmas Star with red flowers is one sip and a Christmas Star with white flowers is another ssp, but the latin name for both of them is the same. One way could be to write the latin name together with the ssp, but the risk is big that the user then will not enter the ssp. By using two different fields I hope to force the user to enter the correct data.
Unfortunately all the dialoges are in Swedish!
I assumed that a batch could have multiple flowers like an arrangement. Since a batch can have only 1 flower there would not be a need for a Batch_flower table.
I cannot see your screenshots, but be aware that (for technical reasons) using an ID and another field while displaying only the second field works only with a popover control, not a dropdown. My Swedish is a bit rusty, but “Nedrullningsbar lista” sounds suspiciously like “dropdown list” … ;-).
Another option would be to use a script to open a popover or a modal window to display a portal (or a separate layout based on the Flowers table) with a list of all flowers, have the user select one, then insert the foreign key and/or the text contents.
Using this method you wouldn't even need to insert the ID, but I guess it would be interesting to know what batches are based on which flowers, or how many batches a specific flower has spawned.
If you want just the flower names you need to uncheck the ouch visa box and set the field field to the flower name. Keep in mind that you are still going to need to get the Flower ID so that you can put it in the Batches::Flower ID field. When you create a batch if you know the flower name at the start you could start this process in the Flower table. Find the flower and then run a script that grabs the flower Id and creates a new record in the batch table with the ID.
I have not tested yet, but I have also been thinking in those lines of creating a script. Displaying the Flower_ID and the common name in a drop down list does not seem to be any problem. So displaying the list of flowers somehow, with a button perhaps, drop over etc. and then have the user simply pic the corresponding ID and enter that in the Batches::Flower_ID. Then have scripts in the fields for "common name", "latin name" and "ssp", that simply uses the Bathces::Flower_ID to copy over the corresponding texts to be displayed.
I will look in to this tomorrow.
Thanks for the help so far.
The "problem" has been solved. Thank you everyone for helping out.