Jump to content

Recommended Posts

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?

 

Screen Shot 2020-06-13 at 23.45.04.png

Sandbox.fmp12

Link to post
Share on other sites
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

Link to post
Share on other sites

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..


213250475_ScreenShot2020-06-15at04_10_40.png.0c2709402d9b56f38eb7e014c1778133.png

If pick a different Vendor ... (Picking ABC Direct)
725594444_ScreenShot2020-06-15at04_11_05.png.5df4c30a810e7621bf2a1bc85ed4b5c3.png

It will update the Vendor_IDfk field as selected. (Perfect)
1390668517_ScreenShot2020-06-15at04_11_19.png.411bd36e2097fbc1fb1f44c3824d0a3f.png

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 ...
1298670966_ScreenShot2020-06-15at04_11_34.png.dce8959a46d6cd5a80df3620e2ff5c24.png

The yellow field does not update:
556074524_ScreenShot2020-06-15at04_11_42.png.b10019185ac2ff079413ef18c70d36ec.png

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

Link to post
Share on other sites
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.

 

Link to post
Share on other sites

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.

Link to post
Share on other sites
Posted (edited)

...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 by Tony Diaz
Question change
Link to post
Share on other sites
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.

 

Link to post
Share on other sites
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.

Link to post
Share on other sites

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.

 

Link to post
Share on other sites
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.

Link to post
Share on other sites
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.

 

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.