Find with variable or summary field in Criteria

[oh4real]

Howdy all,

I can NOT figure FMP out. I am trying to help a friend and thought it would be easy to create some scripts. Turns out not so simple and I can't seem to let go.

I am trying to set up an automatic script that takes the latest date in table bp. Then find all records in an unrelated table ap with dates later than that max. In SQL it would be SELECT * FROM ap WHERE date >= (SELECT MAX(date) FROM bp).

First I tried Set Variable[$topDate; Value: Max(bp::Date)] then use $topDate in the Perform Find[]. When I am at "Perform Find" Specify Find Requests, it won't let me enter variable arguments - always states "The value of this field must be a valid date in the range... [Revert Field] [OK]". I try concatenating - "> " $topDate - no luck error message.

I then added a Summary Field to table bp that is LastDate = Max(Date), which worked as far as Maximum goes, but everytime I try to refer to it, I get nowhere.

I have added a spare bpDate field to table ap and tried to get it to equal the value of the summary field from bp (no luck) or even to be the Max() but since tables are unrelated - no luck.

I am at my wits end. Clearly I am missing something blatantly obvious - or FileMaker has some built in logic that I can't overcome.

ScriptMaker Attempts

Manual Version (works fine)

Enter Find Mode []

Perform Find [Restore] //* with Specify Find Request- Find Records ap::Date MM/DD/YYYY...// *//

Perform Script ["Export_Auto"]

Exit Script[]

Automated Version(s)

Al attempts to replace MM/DD/YYYY...// with variables, arguments, fields have failed:

Clearly I don't know what I am doing, but it shouldn't be this tough. These are following examples at filemaker.com/support/techinfo.html - although the examples given don't even work if you download and unzip the attached files...

Anyway, I assume there is just some subtle architectural difference in FileMaker Pro that I am missing and was hoping someone could help.

Regards,

oh4real

Edited May 20, 2007 by Guest

[David Jondreau]

Variables must be evalusted by the calculation engine. There are many script steps in Filemaker that allow you to access it via a calculation dialog. Perform Find is not one of them. Instead, use this sequence of script steps:

Enter Find Mode

Set Field [fieldName;$topDate]

Perform Find

[aldipalo]

I believe you want:

Set Variable($topDate;BP::Date)

Go to Layout(APLayout)

Enter Find Mode() /* deselect pause

Set Field(AP::DateField;$topDate)

Perform Find()

You must start at the layout where the field resides in order to set the variable and then go to the layout where you wish to find the record in order to perform the find.

HTH

[oh4real]

Thanks folks. Getting there - sorta.

I just figured this Set Field bit out and so now I understand more your responses. I am starting to think of this script stuff more as Macros in Access.

I have worked it out, sorta:

Go To Layout["bp"(bp)]

Set Variable[$$bpDate; Value: bp::maxDate]

Go To Layout["ap"(ap)]

Enter Find Mode[]

Set Field[ap::Date; GetAsDate($$bpDate) & "..." & Get(CurrentDate)]

Perform Find[]

And it worked, according to ascending Date sort of the 1192/1509 records right up until the latest.

Also, there are four records in "ap" that have the bp::maxDate as their date - and when I run the script with GetAsDate($$bpDate) alone it pulls them - and where

THanks heaps for the point in the right direction.

Any notion of there is a way to do this without the "Go To Layout"s ?

Dave

[David Jondreau]

Any notion of there is a way to do this without the "Go To Layout"s ?

Nothing simpler comes to mind. Why would you want to?

[Emma in England]

If your objection to 'go to layout' is the visual disruption to the user, and I can't think of any other, then add a 'Freeze Window' step at the beginning of the script (I only learned about it this week, and now use it all the time!).

And put a 'go to original layout' at the end if you want. It all looks seamless.

[oh4real]

Thanks all. Looking to avoid all the layout changes because I am not a native filemaker guy and all this "go to layout" stuff is a bit of a disappointment, looking for a standard SQL-like command line interface for queries instead of "Find". Just wish there was a way to bypass the user interface - kinda like VBA vs. Macros in MS Access. But it is the architecture of the FMP application that some of my network members will use, so it is what it is.

thanks for the hint on freeze window - i did the trick of "set variable [$$incominglayout;Get(LayoutName)]" as first step in script then "GotoLayout[$$incominglayout]" as my final step. Freeze/original sounds like FMP designers got it.

as a note, here is my final script:

Set Variable[$$inbound_bp_layout; Value:Get(LayoutName)]

Go To Layout["bp"(bp)]

Set Variable[$$bpDate;Value:bp::latestUpload] /* bp::latestUpload is summary field I added to (bp) - Max(uploadDate) */

Go To Layout["ap"(ap)]

Enter Find Mode[]

Set Field[ap::Date;GetAsDate($$bpDate) & "..." &Get(CurrentDate)]

Perform Find[]

Perform Script["Export_to_Autonamed_File"]

Perform Script["New_bp_date"]

Go To Layout[$$inbound_bp_layout]

Exit Script

[Søren Dyhr]

It strikes while reading the thread, why not relate the two tables with a multicriteria relation?

GTRR(SO) seems pretty much up to it!!!

Remember other relation graph methods than the octopussy exists, Anchor Bouy makes an fair efford to prevent clutter!

You scripting wears all the synptomes a poorly structured solution exhibit, cluncky to follow and the use of globals!

--sd

[oh4real]

GTRR(SO) reminds me of Tony the Tiger's "Grrrreat!".

and that is about as far as I understand it. A bit more please - new to FM here.

Also, the tables are completely unrelated. The bp table of dates exists exclusively for the purpose of the script monitoring the dates that uploads occur.

The dates in ap table are transaction dates. But please, expand if you get the chance.

Unfortunately the script "has" to be basic and 'clumky' - this isn't my solution. I am building 'generic', non-intrusive script/solution for 100s of unknown users with existing systems that I have absolutely no control over their implementation. A better, built-in solution would be to add a "uploaded" field to ap and grab all records where it is 'no' or 'null' and then update the field after the upload. But I can't ask users to change existing tables/db structure, they will freak out and decline entirely. However, I can ask them to add an unrelated table.

Given this constraint, if you can think of way to make my script less clumky and more generic, please let me know.

Edited May 24, 2007 by Guest

[Søren Dyhr]

GTRR(SO) is Go To Related Record (Show Only)

non-intrusive script/solution for 100s of unknown users with existing systems that I have absolutely no control over their implementation.

I wonder if it's imposible to make a interface file with all the tables at hand with the passwords your have a.k.a the separationmodel. With that available could you establish your own relations... it's pretty abstract but see if you can get the gist of it from this:

http://www.newcenturydata.com/downloads/separation_demo.zip

The thread header - is misleading!

I then added a Summary Field to table bp that is LastDate = Max(Date)

The criteria isn't a summary field which indeed would have been a challenge requirering a script, but this is a aggregate function, that easily can act as primary key for a relation! This point in direction of pretty liberal assignment of privileges ...this means the separation model idea should work ...there's not even reason for placing this new file on the server!

--sd

Edited May 24, 2007 by Guest