Jump to content

Welcome the ORIGINAL FileMaker Community

Take a moment to join us, no noise, all FileMaker...We Promise

oklord

Scripting Suggestions for getting data from another table

Recommended Posts

oklord

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!
 

Share this post


Link to post
Share on other sites
Steve Martino

You should post your script.

 

Share this post


Link to post
Share on other sites
Josh Ormond

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.

Share this post


Link to post
Share on other sites
oklord

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.

Thanks!

Share this post


Link to post
Share on other sites
oklord

Here's my relationship

image.png.81f0d7263a1de224e6d634c7574a86ab.png

Share this post


Link to post
Share on other sites
Josh Ormond

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 ]

Loop
  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

Share this post


Link to post
Share on other sites
oklord

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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.




×
×
  • Create New...

Important Information

Terms of Use