pc Beginner Question about Foreign Keys

[Marty72]

Excuse my newbie question. I just started using FileMaker in order to build an app for an iPad for the collection of tree data. My database is not very complicated at this point just a couple of 1 to many relationships. Project table that can have multiple Plots. Plots can have multiple Trees.

 

I am having trouble moving my Primary key value into my Foreign key field in my child table to maintain the relationship between my tables. I first tried to set a global variable (in a script) to my Primary key value and than set my Foreign key field calc to this variable, but this didn't work. So now I have tried using Set Field in my script to calculate the Foreign key to the Primary key value - this didn't work for me either.

 

I have attached my relationship graph so you can see the names of my keys. (Not all the values are in these tables yet since I am just trying to test my process first). I have also attached my script. This runs successfully when a button is pressed for adding a new Plot record but the Foreign key field is still blank. I suspect this in not very difficult but I have hit a dead end. Thank you in advance for any assistance.

[eos]

Some notes:

 

1. Don't use a $$global variable (scope/lifetime: the entire file) unless you need to persist it past the end of the script; a $local variable (scope: the script you define it in) will do just fine for your purpose.

 

2. If you assign the variable, why not use it?

 

When you're in the context of plot data, fields from a plot data record are only accessible via a relationship – which you came here to establish in the first place. That is why referencing the primary key field from Plots yields no result  – and why you assigned its value to the variable while you were in the correct context (the Plots layout).

 

So your script needs to read like …

Set Variable [ $plot_pk ; Plots::plot_pk ]
Go to Layout [ TreeData ( TreeData ) ]
New Record/Request
Set Field [ TreeData::plot_fk ; $plot_pk ]

3. Depending on your workflow, you can do this much easier: activate the option "Allow creation of records in this table via this relationship" for your TreeData table occurrence in the Relationship Graph, then use a portal for TreeData on your Plot layout. This portal will have a spare row where you can enter data and so create a new related record automatically.

 

4. This is more a style critique: I suggest giving your primary key fields (and all fields, for that matter) more meaningful names than “index”; and avoiding (needless) abbreviations may be helpful too.

 

I'm also pretty sure that having field names in lower- and/or proper case is more legible and relaxing to the eyes than using allcaps everywhere; not to mention that then you cannot use allcaps anymore where it may be really applicable …

[Marty72]

Thank you for the advice, I will give that a try.

I didn't use the Portal option since all the data entry will be outside in a forest setting on an iPad. I am trying to utilize Popovers, Drop Down lists, etc. to make the data entry as efficient and easy as possible.

[eos]

I didn't use the Portal option …  I am trying to utilize Popovers, Drop Down lists, etc..

 

These features are not mutually exclusive; and a portal doesn't have to be designed as a long(ish) list with narrow rows.

 

make the data entry as efficient and easy as possible.

 

That's always a worthy goal.   Good luck with it.

[Marty72]

So I edited my script as follows:

 

Freeze Window

Set Variable [$proj_pk; Value:TMQUALITY::TM_INDEX_pk]

Go to Layout ["PLOTDATA: (PLOTDATA)]

NewRecord/Request

Set Field [PLOTDATA::TM_INDEX_fk; $proj_pk]

Go to Field [PLOTDATA::PLOTNO]

 

This script is initiated by a Button in the TMQUALITY layout (parent table to PLOTDATA) but when it ends up in the PLOTDATA layout the foreign key is not updated. I have attached my file if this helps you point out my mistake.  (The rest of the stuff is messy but I am just testing this one thing for now). I will take your advice about using lower case.

TMQUALITY.zip

[eos]

There is no such button on that layout, and – more pertinent to your problem – in the file you posted, TMQuality has no records – so which project would that plot be related to?

 

Guard against this by exiting the script (and alerting the user) if the found set is empty.

 

Create a new project record, and you will see that the script works.

 

EDIT: I see – “Go to Plot Data Entry” is a button, and has an OnObjectEnter trigger for the script to create a new plot record. Not only does that not work, it's also confusing.

 

Let your UI elements and scripts do one thing each, and do that properly …

[Marty72]

Now I feel a bit stupid. I was creating a new project before proceeding to the plot layout for testing purposes. However, your point about the button also having a script trigger was the issue. Once I removed the script trigger and just had the button perform the script, the foreign key is updated with the correct value and shows up on the Plot layout.

Thank you.