Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Scripting Suggestions for getting data from another table


Recommended Posts

Hello FileMaker Forum!

I'm a Newb, been using FM 16 for about 3 months, but I'm an old dBase programmer from the day.

Thanks in advance for assistance!

I have a button on a form that when pressed/clicked calls a simple working script that I'm trying to add an if statement to.

In the form the user has already selected a budget category from a pull-down list taken from Categories::Category and the table ID gets stored in field CheckBook::CAT1.  The user also enters into field "CheckBook:Cat1 Amt" a dollar amount of an expense.  Tables Checkbook and Categories have relationships set.

The script works except for my new IF statement.  What I'm trying to do is to replace the amount in "Categories::Category Total" with a new amount by doing Categories::Category Total - CheckBook::Cat1 Amt

I can see by using the debugging tool that the other table isn't focusing on any record possibly indicating my relationship needs tweaking or I need to move the record pointer to the correct record in the other table to be able to do a set field.  I'm not sure what functions, etc to use.

Thanks again!

Link to comment
Share on other sites

A couple of small notes and a thought on your issue.

1. It looks like there is a "?" in the field name of CheckBook::Totaled? I would highly advise against that practice. Those special characters can cause unexpected behaviors sometimes.

2. Why are you using "Insert Text" in step 5?

3. When setting fields across relationships, the Set Field ( or any step except a couple ) will act on the 1st record in the relationship. You can get around that if you are working in a portal, or isolate the record you want update using another relationship.

Link to comment
Share on other sites

Thanks for the thoughts.  I did change the field name and took out the ?.  Great thought!

Step 5 works and simply let's me know that transaction has been deducted from the checkbook total.

So my Set Field goes to the 1st record which isn't what I want.  How do I change records?  The checkbook:cat1 has the category the user has selected so somehow I need to get the categories table to jump to the corresponding record.  Not sure how to do this.


Link to comment
Share on other sites

Ok. So for the Insert Text, you can just use Set Field. Insert Text will work, but there are a few behaviors, that over time, you may run into. The biggest is that the field is required to be on the layout, and editable. In many cases, you don't want the user to be able to edit that field. Using Set Field allows you to set that value in the record itself, and then you can just display the field without allowing the user to edit it.

Set Field [ Checkbook::Totaled ; "Done" ]

For setting fields in related tables, there are a few ways to do it. 

1. Go to a layout based on that related table. Do a find to narrow down the records, and loop them and set the field you want. If that layout is based on the same Table Occurrence ( TO ) as the relationship, you can still reach back into the parent to grab an needed values.  This approach works, but has one flaw, if something fails in the middle, you may not now one or more of the records didn't get updated.

2. Transactions. Todd Geist of Geist Interactive has published a lot of info on transactions ( database transactions ) in FileMaker. Essentially, it ensures all of the data gets set, or none of it does. The idea would be, use a portal to loop through using the Go To Portal Row script step and set the field that way. Inside the portal, your Set Field pointed at the related field will then act on the specific record you are sitting on in the portal.

Basic script would look something like:

Go to Object [ "portal.Name" ] // It is beneficial to give the portal an object name. So you can be sure you get into the correct portal if there is more than one.
Go to Portal Row [ First ]

  Set Field [ Categories::Category Total ; <whatever your calc is for this> ] // The TO in the field reference needs to be the same as the TO for the portal itself.
  Go to Portal Row [ Next ; Exit after last ]
End Loop

Commit Records

Those are just 2 of the easier options. There are others, as well.

  • Like 1
Link to comment
Share on other sites

thank you so much.  I'll be studying this out.  I did find a handy tool "Date Viewer" and am using it to show me various values and hopefully that will help me figure things out.

Link to comment
Share on other sites


  • Create New...

Important Information

Terms of Use