Then you should build a relationship based on Box and then have a Calculated field using List() - function to get your data
That List -function will get your data with enter in between each row, so make a Substitute on enter to ", " and you will get a "one row" with all your names in same box
when a list is expected, ExequteSQL seems to be the fine
ExecuteSQL("SELECT shipment, box, item FROM YourTable WHERE item=?";"";""; YourItem)
You can define a $$Variable with this statement, it will contain the list, can be printed, viewed
(take care of correct names... SQL is case-sensitive, some special characters need to be masked - there are quite some examples available)
There is a Summary field type called List of:
Once you do a FIND (for 'abc') the value in the field would be a return-delimited list of whichever field ("Box #") you summarize.
Box_List = List of ( Box # )
this can be copied to use elsewhere. Once you get the list, you can use it as a multi-line key (into a global) and return all items from those boxes.
I see another reply with ExecuteSQL() and that would get you the "List" which can be used to query to get all items.
thanks for your help so far
I have created the global field in which I have pasted the unique list of box nr’s… How do I create a find request using the global field?
Dennis, a 'trick' seen in FileMaker but no other db:
a return-delimited list (when used on one side of a relationship) can match any of the values on the other side of the relationship. A relationship is a kind of "find" if you think about it.
the script step "Go to related record" (or GTRR as many call it) will allow you to navigate from a parent to the related children and only the related children (check the dialog carefully).
if your global field is on a parent record (any parent) and the GTRR is called, the resulting set is all (actually any) match in the child records!
once you are on the layout with your "found set", you can sort as needed for showing your report however you need or using the data in other ways (looping gathering?)
thanks, almost there I guess… quite new at this…. so hopefully last question… you mention relationship, what do I need to relate? The global field with the field box nr?
I now have one lay out showing the table
Second layout with the global field showing only those list of unique box numbers I need…
Final step is to make the “find”in my table so it shows the box numbers including all items from the initial search….
the global field (with the list of whatever) that would match the field with the same type of data. I thought you were matching the Item.
The GTRR is calling the relationship (when you are ON the 2nd layout) and choosing the 1st layout to display the result. this 1st layout should have the columns/fields you need already, including sub-summary parts if desired.
Post your dialog if needed.