SET FIELD script for cabinet shop math

[Jarvis]

I am commencing a massive re-write of the database we use to run our cabinet shop.   This database involves a lot of fields and a lot of scripts.  Much of the database is managed on a desktop computer but lots of the information is passed to an iPad through Filemaker Go.

I want to make the scripts as efficient as possible.  Many of them now require too many round trips to the server.   I am hoping to do this with a SET FIELD script.

I am trying to develop a script that collects data from the current record of one table and posts it into a new record on another table. This works fine if I move back and forth between layouts using copy & paste.  When I try to do this with SET FIELD it always returns the values from the first record and ignores the values in the current record.

Can SET FIELD pass this kind of data or am I just doing something wrong?

I have attached a sample file of what I am trying to accomplish:  The protocol is as follows:

1)  We import cabinets from the cabinet library

2)  We develop cost estimates on a synopsis sheet to predict labor

3)  If we get the job we go back to each cabinet on the library page and corroborate the dimensions.  When they have been audited we then post the record to the MasterLIST.  It is this last step I am having trouble with.

 

setfield example Copy.fmp12

[comment]

I did not fully understand your description or your file. With regard to:

1 hour ago, Jarvis said:

am trying to develop a script that collects data from the current record of one table and posts it into a new record on another table. This works fine if I move back and forth between layouts using copy & paste.  When I try to do this with SET FIELD it always returns the values from the first record and ignores the values in the current record.

In general, it is best to avoid duplicating data. In cases where it's necessary - for example, copying the list price from a Products table into a new record in LineItems table - it is customary to establish a relationship between the two and use it for a lookup. 

If for some reason a lookup is not suitable in your situation, you can avoid the "move back and forth between layouts" by using variables to carry the data - for example:

Set Variable [ $id; Cabinet Library::Cabinet Number ] 
Set Variable [ $style; Cabinet Library::Cab Style ]
Set Variable [ $width; Cabinet Library::Width ]
Set Variable [ $depth; Cabinet Library::Depth ]
Set Variable [ $height; Cabinet Library::Height ]
Go to Layout [ “Masterlist” (MasterList) ]
New Record/Request
Set Field [ MasterList::Cabinet ID; $id ] 
Set Field [ MasterList::Cab Type; $style ]
Set Field [ MasterList::Width; $width ]
Set Field [ MasterList::Depth; $depth ]
Set Field [ MasterList::Height; $height ]
Commit Records/Requests

However, it seems that doing just:

Set Variable [ $id; Cabinet Library::Cabinet Number ] 
Go to Layout [ “Masterlist” (MasterList) ]
New Record/Request
Set Field [ MasterList::Cabinet ID; $id ] 
Commit Records/Requests

should be sufficient - provided you have a relationship between the two tables based on matching:

Cabinet Library::Cabinet Number = MasterList::Cabinet ID

With such relationship in place, once the MasterList::Cabinet ID field has been populated, the other fields can lookup the data from the corresponding record in the Cabinet Library table. Or you could continue with what you have now:

Set Field [ MasterList::Cab Type; Cabinet Library::Cab Style ]
Set Field [ MasterList::Width; Cabinet Library::Width ]
Set Field [ MasterList::Depth; Cabinet Library::Depth ]
Set Field [ MasterList::Height; Cabinet Library::Height ]

and have it fetch the data from the correct record - that is, the related record - in the Cabinet Library table.

For reasons that I cannot understand, your file has the two tables related by matching on two global fields. Such relationship is invalid.  It makes all records related to each other. This is why you get the data from the first record in Cabinet Library: when referencing a related field, the data is always fetched from the first related record. Keep in mind that a relationship ignores the found set, sort order or the current record of the related table.

 

 

Edited April 14, 2019 by comment

[Jarvis]

Thanks Comment.  That is just what I needed.

FWIW:  I am a much better cabinet maker than FM programmer.

[Jarvis]

Comment,

I just entered the code as you described and it works just like you said it would.  Very inspiring.

Thank you so much.  

 

[Jarvis]

Here is a comparison of the way I was scripting this before being edified by Comment. 

This database posts all the math for six possible drawer box combinations.  When everything is posted it will get exported as a CSV file to a standalone application that optimizes the most efficient way to cut plywood.   I have no control about how that software imports the CSV.  I just have to make my data show up in the right columns.

If you compare the two upload methods you will see that Comment's recommendation seems to take about half as long.  This is on a desktop computer.  I imagine the difference would be much greater if you had to move all those packets of data back and forth using Filemaker GO on an iPad.

I don't know enough about fetching data with a lookup to know if this would be viable for me.  The panel optimizing software needs all the data to show up in one place but the cabinet information derives from many locations.

To simplify testing I have included yellow buttons to populate cabinet data fields.  If  you are interested in testing the "search & destroy" button it is best to change the (yellow) cabinet ID number at left.  The reason for this button is that information is always changing in cabinet land.  We need  the ability to seek out any instance of cabinet math and delete it from the cutlists when the information changes.

I just thought I would put this up to show how much better Comment's approach is than mine was.  Since drawer boxes are just a tiny portion of the entire conundrum I can only imagine how much better it will run for this change in appproach.

Box Math Copy 1.fmp12