Jump to content
Sign in to follow this  
Marmot

script question

Recommended Posts

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

Share this post


Link to post
Share on other sites
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. wink.gif

Share this post


Link to post
Share on other sites
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...

Share this post


Link to post
Share on other sites
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 ]

Share this post


Link to post
Share on other sites
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... wink.gif

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

smirk.gif

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
This thread is quite old. Please start a new thread rather than reviving this one.

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.

Sign in to follow this  



×
×
  • Create New...

Important Information

Terms of Use