Set Variable missing?

[Jason Feinberg]

I am using Filemaker Pro 7 and trying to make some scripts.

I cannot locate the "Set Variable" function - is this new to 8 or only in the Developer/Advanced versions? It seems odd that there are all these other functions that do much more advanced things, but I cannot simply set a variable.

Here's what I want to do - is there another way? I want to simply set a variable to the value in a field, then be able to use that variable (searching, record lookups, etc).

Thanks,

Jason

[LaRetta]

Hi Jason,

Variables appeared with vs. 8. In vs. 7, you can use script parameters and/or globals. In your case, since you want to capture the value of a field, just write your script as normal. But attach the script to a button (or directly to the field itself) by right-clicking on the object and specifying the script there. At the bottom, is a box to hold your script parameter. Then within your script, replace the field value with Get(ScriptParameter). If you get stuck, give us the specifics and we can help you through it but for searching, it might look something like:

Enter Find Mode [ ]

Set Field [ dataField ; Get(ScriptParameter) ]

Perform Find [ ]

The script parameter is picked up from a User button-click and lasts the duration of the script OR it can be passed to another script which is called within the original one. Variables in vs. 8 extend the functionality TREMENDOUSLY. Not only is User button-click no longer required, but multiple variables can be named, viewed and used and we can also use global variables which persist after script terminates.

LaRetta :wink2:

[Jason Feinberg]

Thanks, that is working great - the value of the variable is getting passed. I do, however, have to do a "GetAsText ( Expenses::Project ID )" for the ScriptParameter as the field format is a number, but it works great.

Now my only question is this - how can I use this data (text) to Find all the records that match? Keep in mind the field is a number.

When I make a Perform Find script step, click Specify Find Requests, click New, and set Expenses::ProjectID = 110, it performs the find as expected and returns the correct records.

With 110 being passed as the ScriptParameter to the script, this is what I'd think would work:

in the Find Request, put = GetAsNumber(Get(ScriptParameter))

However, this returns no records. So, how can I use the ScriptParameter (which is text) to do a Find script action? Are you allowed to use functions in the Find Request?

Thanks,

Jason

[LaRetta]

If you will notice, Jason, I didn't store a Find Request because you can't put formulae into them. You must use Set Field[] and set the field directly with the script parameter. Notice there is no pause on the Find[] either ...

You will also want to add a Set Error Capture [ On ] (and Allow User Abort [ Off ] ) prior to the find and trap for 'no records found' yourself. And you can forget problems between text and number here; FM will translate them properly but it is important to always pay attention to data types and I would question why you have a mismatch. Script parameters have NO PROB with numbers - you can even put graphics on them.

If you need help writing the error trapping portion of the script or have any more problems, we're here to help you through it.

UPDATE: To explain 'FM will translate them' ... I just mean that you can put text in a number field and FM won't balk - and vice versa; math can happen in a text field, etc. Other data-types are quite picky. So mixing text/number fields should be considered each and every time - and if the thinking is clear then the data-types seem to naturally fall in line.

LaRetta

Edited March 20, 2006 by Guest
Added update

[Jason Feinberg]

Hi LaRetta,

Thanks - makes perfect sense. Also, I just now saw the script code you posted - in the email I received it only had the first paragraph, so I thought that was your whole message. Doing it the way you show above is simple and works great!

Thanks again,

Jason

[Jason Feinberg]

Ok - one last (i hope) question -

At this point I have the Record Set of all records with a certain project ID. On the layout, there is an Amount field.

If I have 3 records returned, I want to Show Custom Dialog with the SUM of the Amount field of all three records. If I do the dialog and do a Sum() calculation on the Expenses:Amount field, it only sums the active record. How can I make it sum ALL Expenses:Amount fields in the found record set?

Thanks again for all your help!

Jason

[LaRetta]

How can I make it sum ALL Expenses:Amount fields in the found record set?

A columnar List layout with grouped data and sub-totals should work nicely and provide ultimate flexibility. You might also consider a restricted relationship.

Grouped columnar List with sub-totals: Select ProjectID and Amount fields for the report. Category should be based upon the ProjectID. Sort by the ProjectID as well. Create a summary field called sTotalAmount and place it in the leading part along with the Project Name or ID. Delete the body if you wish. Sorting by this break field (ProjectID) is critical in grouped reports so I always suggest letting FM create the report for you (so your sort criteria stores within the script).

Restricting a relationship: Those ProjectIDs would need to be placed into a global multiline to constrain summarizing to the found set. Loop portion of script to accumulate the ProjectIDs would be:

Freeze Window

Open Record/Request

Go To Record/Request/Page [ First ]

Loop

Set Field [ global ; global & ProjectID & ¶ ]

Go To Record/Request/Page [ Exit after Last ; Next ]

End Loop

Commit Records/Requests

The relationship would be: [color:blue]MainTable::global = anotherOccurrence::ProjectID. Then create a field called cTotalFound (number) with: [color:blue]Sum ( anotherOccurrence::Amount). Method 2 works well if Users want to view the field on their current layout. But if the record-count is large, the loop takes longer (DOH) and the Sum() aggregate field will also take longer to display.

LaRetta :wink2: