Script to Get Related Field Value?

[Tony Diaz]

I made a sandbox version of what I'm trying to do.

When I make an entry on the left column, in one of the red titled fields, it's using a value list that is based on existing values in a related table of that name.

If I type in something that is not on that value list, I get an "Add" button next to the field name. Click it and I've now created that "new vendor/publisher/developer".

That's all good.

If I pick something that is on that value list, I would like to load it's key (Vendors::ID) into Items::Vendor_IDfk so that I can relate the current record in Items to that Vendor.

...and why do the totals in the footer not work for the related tables?

 

Sandbox.fmp12

[comment]

36 minutes ago, Tony Diaz said:

If I pick something that is on that value list, I would like to load it's key (Vendors::ID) into Items::Vendor_IDfk so that I can relate the current record in Items to that Vendor.

See if the attached demo helps.

 

37 minutes ago, Tony Diaz said:

and why do the totals in the footer not work for the related tables?

Not sure what would be considered  as "working" in this situation. An item record can see at most one related record in each of the parent tables. If you want to know how many items in the current found set have a vendor, then use a summary field (defined in the Items table) that counts the Vendor_IDfk field.

SelectOrAddParent.fmp12

[Tony Diaz]

Thanks! Using your example, my Vendors is Parent and Items is Child. (Attached)
I see that part of it doesn't depend on scripting at all..

If pick a different Vendor ... (Picking ABC Direct)

It will update the Vendor_IDfk field as selected. (Perfect)

But if I were to pick a vendor from the ID,  if I want to change the yellow field I probably need to have a script trigger on that commit ...

The yellow field does not update:

This probably requires a script.. (...and a trigger on modify )

...which I figured out while posting this reply...

Set Variable [ $ChangeVendor ; Value: Vendors::Name ] 
Set Field [ Items::Vendor ; $ChangeVendor ] 

So yeah, two line script on the Items Layout, Vendor_IDfk field, with a trigger of on modify. If I understand the terminology correctly, "..will be run when the (Vendor_IDfk field) is modified when active."...

Key part being "when active.." is when I selected the field and did that change, but not if the field is changed by FileMaker calculation or script.  Otherwise it might get into a loop.

So, yup. With that, I get the flow I want: I can relate / choose from a list of existing choices for that record, get a visual indicator that what I've entered is a new value, and have the option to store that new value in the value list for retrieval on other records.

Or leave it as not added, no associated record and the "Add.." button just stays 'lit'. 

Thanks for the example.
 

SelectOrAddParent2.fmp12 Sandbox.fmp12

[comment]

4 minutes ago, Tony Diaz said:

I see that part of it doesn't depend on scripting at all..

Correct. You need a script only if you want to create a new parent record.

 

5 minutes ago, Tony Diaz said:

But if I were to pick a vendor from the ID, 

I would suggest you standardize on a single method for picking the vendor. Otherwise you will find yourself adding more and more complexity to the solution. The method suggested in my file is already more complicated that I would like in order to accommodate your wish to either select or type a name using the same field. I would have preferred selecting from a card window, using a find to narrow down the selection.

 

[Tony Diaz]

Good points, yeah- I just figured I'd 'round it out' and make the same work from either approach. So I know I can do something similar from either direction. I won't set something up for someone else like that, rather I'd stick with a single method for consistency and simplicity.

But in this case, I actually prefer this over the card window. Because if I type in that field and don't jump to an existing selection within a couple three key entries, or even just using the keyboard to enter something rather than picking from drop down options, I'm likely going to type in new data. This is perfect,. though yes, different.

But it also gives me the flexibility that I was looking for in another thread. Relate the record, or not, add the new entry to the ValueList, or not, and in effect not have to worry about being warned over duplicate entries as by design, I'd either get a completed existing choice by typing it fully out, or seeing it and clicking it. So, yes, the opposite of checking for duplicates.

Thanks

Now, on those totals in the footer. As you can see, Total Items shows a value, but the others are showing zeros.
They're all the same calculation, in their associated tables: Get ( TotalRecordCount ) of (Items, Vendors, Publishers, Developers).

But only the Items one is correct. Vendors is showing 5 because I changed that one to ValueCount ( Vendors::Name ) .. but 5 isn't correct. There are actually 8 unique records there now. Is this because of the table that the layout is based on?

What I'm trying to get is a running total of total of each of those categories  (record types) as a live status.

A summary field (defined in the Items table) that counts the Vendor_IDfk field (in the items layout) has the condition to not be accurate. There are vendors / publishers / developers in their associated tables that are -not- related to any other records, too.

[Tony Diaz]

...and that would be a Summary Field... Imagine that! Doh!

...that I'm imagining needs to be assigned to a global variable as each is only working for only the table that is selected for that layout. 

...and then a trigger that if that value changes. Or can I do a summary as a calculation so I can then Set Variable $$Name right afterwards?

 

Edited June 15, 2020 by Tony Diaz
Question change

[comment]

3 hours ago, Tony Diaz said:

Now, on those totals in the footer.

I am afraid you still haven't told us what exactly those fields are supposed to show.

 

[Tony Diaz]

3 hours ago, Tony Diaz said:

What I'm trying to get is a running total of total of each of those categories  (record types) as a live status.

As I noted above, and then the post afterwards was speculation that didn't work anyway. So the question still stands. I'd like a running total of amount of Items, Vendors and Developers. Not the found set. The actual amount of rows/records.  That can be counted by the ID in the respective table, but how to get them to show all on any layout where that field is placed. I think I even tried merge fields.  I can only get the total Items on the Items layout, Vendors on the Vendors layout, etc. Otherwise I get zeros.

[comment]

If you want a count of all records in the Vendors table, with no regard to any of the records in the current found set of the Items table, you could count the values in the value list you already have:

ValueCount ( ValueListItems ( "" ; "VendorNames" ) )

Alternatively, you could define a calculation field =

 Get ( TotalRecordCount )

in the Vendors table and place it on the layout of Items. You say you already tried that - make sure the field is unstored.

 

[Tony Diaz]

14 minutes ago, comment said:

make sure the field is unstored.

 ✔️ ... and now it works.

Funny. Nothing I read about that function in the context of a summary said anything about storage.

[comment]

11 minutes ago, Tony Diaz said:

Nothing I read about that function in the context of a summary said anything about storage.

The storage thing is not specific to any one function. Any stored calculation field will recalculate only when a field it references is modified. Since this calculation references no fields, it will calculate once when the record is created and never again.

The more interesting thing about Get ( TotalRecordCount ) - which I believe should be mentioned in the help -  is that it returns a value  even if the child is orphaned.