script question

[Marmot]

I'm trying to solve the puzzle below. Digital Man has said I can do it if I run FMP7. Luckily I have it. Can someone explain how I should use FMP7's capabilities to solve the problem?

 

THE PROBLEM

I have three tables related via Invoice ID and Job ID. The tables are

COSTS

EXPENSES (job specific costs)

INVOICES

 

What I'd like is to create a script in EXPENSES which does this....

- takes values put into an INVOICES record (job ID, start_date, end_date) as criteria to perform a find operation in COSTS

- generates a found set in COSTS

- imports the found set into EXPENSES (the found set is the full listing for the client's information of chargeable costs for the time period shown in the invoice)

 

I've already set up a system which uses portals but that delivers a non-static view of the information. I need static info so I have a record (for tax purposes) of what I charged at a particular time.

 

The portal also limits the amount of info you can show because of its limited size. Is there a way of dealing with that?

 

regards

 

Marmot

[CobaltSky]

Hello Marmot,

First let me say that there are several ways you might go about achieving the functionality you have described - with or without FileMaker 7, in fact.

 

Essentially, there is nothing stopping you scripting the procedure pretty much as you've described it. Ie transfer values from the current record in Invoices to a global field, then have your script switch to an appropriate layout in Costs, enter find mode and retrieve the data from the global field, entering it into the appropriate fields as find criteria. Your script can then execute the find, establish a new window, select a layout based on the Expenses table and import the found set from Costs - then return to the Invoices layout.

 

As regards portals, they can be set up to provide a scroll bar (its an option in the Portal Format dialog) so that you can view an unlimited number of records in the one portal.

[bikergeek]

You could export records from the found set to a static file (text, excel, etc.) as a last step in the script, and then store a reference to the exported field file in a history layout container field...

[Marmot]

Thanks for this. I don't how to how to use Globals.

 

Step 1 must be making 3 Global fields in INVOICES to temporarily store info about job_id, invoice-start-date, invoice-end-date.

 

Is this done w Set Field (job_id_global, job_id) etc?

 

Then shift over to COSTS layout, change to find mode, then Set Field (job_id, job_id_global)?

 

Perform find

Export Records (found set) to EXPENSES.

 

...something like that ??

 

tom

 

[ QUOTE ]

 

 

Essentially, there is nothing stopping you scripting the procedure pretty much as you've described it. Ie transfer values from the current record in Invoices to a global field, then have your script switch to an appropriate layout in Costs, enter find mode and retrieve the data from the global field, entering it into the appropriate fields as find criteria. Your script can then execute the find, establish a new window, select a layout based on the Expenses table and import the found set from Costs - then return to the Invoices layout.

 

 

[/ QUOTE ]

[CobaltSky]

Hi Tom,

Yes, first create the global fields in the Define Database dialog, then set up your script to use the Set Field[ ] command to transfer the required values to and from the globals.

 

Sounds like you have the gist of the rest of the script procedure...

[Marmot]

thanks Cobaltsky (UK winter skies aren't)

having made the solution you suggested work now (after a fashion) the question of how to store the found-set remains. I cld export it as a text file as la de dah oof! suggests. I wonder if there's another way.

 

tom

[Maarten Witberg]

A possible solution to create retrievable found sets:

 

1: create a second table called Foundsets, holding an auto-created ID; a text field for the name of the found set, maybe a timestamp field (could in this case be also used as ID); a global for temporarily storing the ID called gTemp.

2: to the data table you wish to perform the finds on, add a text field (called Keys) and a global text file (called gHop); create a value list based on foundset IDs and secondary, foundset names; link this valuelist to the global;

3. create a self-join relationship in the data table between gHop and the Keys field; put a button in the layout nex to gHop that calls Go to related records (show only related)

4. to the find script, add script steps that

a- allow the user to enter a name for the found set (if bigger than 0 records found) b- store the foundset ID in the global c- add the ID to the Keys field in the found set using:

 

 

Code:

---

#create found set
if [get(foundcount)>0]
go to layout[foundsets]
new record
set field [gTemp;ID]
show custom dialog[enter name for foundset]
go to layout[datatable]
go to record[first]
loop
Insert Calculated Result [keys; gTemp&"¶"] 
go to record[exit after last, next]
end loop
end if

---

 

 

if you need further assistance, I can make a small sample

 

kjoe

[Marmot]

these replies to my queries give me plenty to get my teeth into and also point me in the right direction..thanks Kjoe, Cobaltsky and mr la de dah oof! I'm off and running

 

tom