"Go To Related Record" won't go there (or even relate to the parent record)!

[Guy Smith]

This is only my second attempt at writing a script, so please bear with me for the exceedingly high noob factor!

Background: My club has some members that pay their dues annually and some that pay every month. I'm trying to build a script that will find the monthly dues payers and automatically fill in the date, reason, and amount fields in a related record.

I am using two related tables called 'People' and 'Expenses'. I identified the Monthly Dues payers in the People table and allow creation of records in the Expenses table.

The script that I have come up with so far properly finds the records of the monthly payers in the people table and the first time it goes to the related record in the expenses table, opens a new record and fills in the three fields I want it to, BUT the expense isn't tied to the parent record.

Additionally, when it runs through the script the second time, it generates a new record in the People table instead of going to a related record in the Expenses table.

Here's the (obviously reaaaaaaaalllly bad) script:

Go to Layout [ “PeopleList” (People) ]

Perform Find [ Specified Find Requests: Find Records; Criteria: People::Dues Payments: “="Monthly"” ]

[ Restore ]

Go to Record/Request/Page

[ First ]

Go to Related Record [ From table: “Expenses”; Using layout: “Expenses” (Expenses) ]

New Record/Request

Go to Field [ Expenses::ExpenseDate ]

Insert Current Date [ ]

[ Select ]

Go to Field [ Expenses::ExpenseReason ]

Insert Text [ “Monthly Dues for Previous Three Months ($5.00 per Month)” ]

[ Select ]

Go to Field [ Expenses::ExpenseAmount ]

Insert Text [ “15” ]

[ Select ]

Commit Records/Requests

[ No dialog ]

Loop

Go to Layout [ original layout ]

Go to Record/Request/Page

[ Next ]

Go to Related Record [ From table: “Expenses”; Using layout: “Expenses” (Expenses) ]

New Record/Request

Go to Field [ Expenses::ExpenseDate ]

Insert Current Date [ ]

[ Select ]

Go to Field [ Expenses::ExpenseReason ]

Insert Text [ “Monthly Dues for Previous Three Months ($5.00 per Month)” ]

[ Select ]

Go to Field [ Expenses::ExpenseAmount ]

Insert Text [ “15” ]

[ Select ]

Commit Records/Requests

[ No dialog ]

Go to Related Record [ From table: “People”; Using layout: “PeopleList” (People) ]

End Loop

Any help would be greatly appreciated and if anyone can point me towards a good resource for learning scripts, I'd certainly appreciate that as well.

Thanks much!

Sincerely,

Guy

[LaRetta]

Hi Guy,

Your GTRR script-step can't work because, unless there is already a previous Expenses record, it can't go there. Regardless of whether there is already a prior expense record for each Client, you want to create a new Expense record, so your script can look like this (pseudo-script):

Go to Layout [ “PeopleList” (People) ]

Set Error Capture [ On ]

Perform Find [ Specified Find Requests: Find Records; Criteria: People::Dues Payments: “="Monthly"” ] [ Restore ]

If [ not Get ( FoundCount ) ]

Show Custom Dialog [ OK ; "No records found" ]

Show All Records

Go To Layout [ original layout ]

Exit Script

End If

Go to Record/Request/Page[ First ]

Loop

Set Variable [ $personID ; People::PersonID ]

Go To Layout [ Expenses based upon Expenses table ]

New Record/Request

Set Field [ Expenses::PersonID ; $personID ]

Commit Records/Requests

Set Field [ Expenses::ExpenseDate ; Get ( CurrentDate ) ]

Set Field [ Expenses::Expense Amount ; 15 ]

Set Field [ Expenses::ExpenseReason ; “Monthly Dues for Previous Three Months ($5.00 per Month)” ]

Go to Layout [ “PeopleList” (People) ]

Go to Record/Request/Page [ Next ; exit after last ]

End Loop

I modified the script to add error capture.

Just Added: Also note that I use Set Field[] instead of Insert Date or Insert Text ... that is because they are safer, more flexible script steps. "Insert" steps must have a field present on the layout or they will fail. Set Field[] has no such limitation.

[Guy Smith]

LaRetta:

Thank you VERY MUCH for your time and assistance - I'd definitely be sunk without it! I didn't realize there was a category below "Noob", but I think I'm there! :-)

I tried to run the Set Field scripts and for some reason they won't put any data in the fields, so I'm sure I have something out of whack: When I try to use Set Field, I cannot get the wording to look like your example: The semi-colons refuse to show up and the closest I can get is to put in some Equals signs. When you select the Set Field script step, which options do you use? The 'Specify' button to the right of the 'Specify Target Field' check box (and do you check the box?) or am I supposed to use the 'Specify' button to the right of the 'Calculated result' button?

I've also read that the Set Field script step is safer than the Insert... steps - thanks again for making my database a little more failsafe!

[LaRetta]

There are two parts to the Set Field[] script step:

1) Specify target field where you find the field you wish to set

2) Calculated result where you enter the value

So in your example, the fields you are setting are the fields in Expenses and the values will be as follows (and only put the portion of the below in blue into the calculation dialog:

Set Field [ Expenses::ExpenseDate ; Get ( CurrentDate ) ]

Set Field [ Expenses::Expense Amount ; 15 ]

Set Field [ Expenses::ExpenseReason ; “Monthly Dues for Previous Three Months ($5.00 per Month)” ]

Note that you can select and insert fields to represent your values (the portion in blue that you enter in the calculation dialog) or you can enter a function as in Get ( CurrentDate ) which inserts the current date or you can insert static text. If static text, numbers do not need to be enclosed with quotes (as in the Amount)) but text values do (as in the Monthly Dues reason).

If you still get stuck, I will be happy to look at your file.

[Guy Smith]

LaRetta:

You Rock, You Rock, You Rock!!!

Despite all of my reading, it didn't sink in until you broke it down with there being two distinct steps to the Set Field step - I can't thank you enough for sharing your brain cells and making me a little smarter: I made the corrections and it works perfectly.

Thanks to you, a chore that used to take over two hours each time now takes just a few seconds.

Wishing you all the best in the new year!

Sincerely,

Guy