Auto-populating Estimates with Estimate Lines - Template best way?

[geraldh]

Hello,

 

I am using FM to manage data for an event management company.

 

I am looking for a way to auto-populate Estimate Lines for an Estimate of a particular Event.

I have found that certain Event Types (Dinner, Cocktail Reception, etc) will have same/simliar Estimate Lines (i.e. the same same collection of Items, with same/similar Quantity, Hours, etc).

 

I am currently doing this by having a separate "Estimate Template" table that mirrors the fields in the Estimate Lines, with quantity and hours values inputted.

I run a script that will find the appropriate Template records that match the Event Type.

The script will then export these found records and then import them into the Estimate Lines.

With a loop I have the appropriate EventID insert into the EventIDfk fields.

 

But I keep thinking there is a better way...I feel this is kind of a sophmoric method....

 

Would it better to do script that will create new record, do a lookup from a pool from other tables (such as Inventory, Staff, Venue), and have the quantity, hours populated from some template.

 

I wish to apply what I learn from this to do similar processes with auto-populating Crew Calls, Run Sheets, again, depending on the Event Type.

 

Any suggestions or warnings is greatly appreciated!

[Ocean West]

There are several ways to do this:
 
1. capture ID, Export / Import  (template of items) update IDs after import.  [slowest]
 
2. similar but use import from FMP to FMP  different tables ( no need for export )  (not safe for Server Side Scripting ) 

 

3. goto templates - set global fields - validate relationship to child table - loop thru setting related data auto create. rinse repeat.
 
4. use an SQL plugin - (many to choose from) use an Insert SQL statement.

 BE_FileMakerSQL( "INSERT INTO ITEMS ( invoice_id , item , amount )¶VALUES " & invoice::sqlValues  )

in my case my data in my sqlValues is data from another table.

 

( '218' , 'Stage' , '588.63' ) ,  (  '218' , 'Gas' , '46.34' ) , (  '218' , 'Electric' , '29.66' ) , (  '218' , 'Storage' , '0' ) , (  '218' , 'Late Charge' , '0' ) , (  '218' , 'Rent Credit' , '0' ) , (  '218' , 'Security Deposit' , '0' ) , (  '218' , 'Security Fee' , '0' ) , (  '218' , 'Miscellaneous' , '0' ) , (  '218' , 'Credit Forward' , '0' ) , (  '218' , 'Balance Forward' , '0' ) , (  '218' , 'Water' , '' ) , (  '218' , 'Trash' , '' ) , (  '218' , 'Sewer' , '0' ) , (  '218' , 'Write Off' , '0' )

 

 

One statement crated 15 records in another table with out even having to change context. 

 

here is a few video examples http://screencast.com/t/zDYMpyPWLoo   http://screencast.com/t/lIdBGcvFL4H4

 

 

hope this helps.

[geraldh]

Thank you Stephen for the options and the cost of the different options. This makes more more confident proceeding from here.

 

My SQL skills are non-existent, but that is something on my to-learn list.

 

I will check out the videos....(might be back if I run into another problem!)

 

Thank you again

[geraldh]

There are several ways to do this:
 

3. goto templates - set global fields - validate relationship to child table - loop thru setting related data auto create. rinse repeat.
 

 

 

 

I have decided to go with this method (for now, until I get more comfortable with SQL). What I am having problems is that my  "Template" will have records like this:

 

Staff: Bartender

Quantity: 8

 

Staff: Usher

Quantity:40

 

Staff: Supervisor

Quantity: 2

 

I wish to make 8 new records with "Bartender" in the position field; 40 new records with "Usher" in the position field; 2 new records with "Supervisor" in the position field.

 

Using Loops and Global counters, etc I have been able to create new records for the first position.

; but I am not sure how to do this for multiple positions. I thought of having multiple variables, but the number of positions that I might have to create records for varies with Event Type (for instance, some Event Types may need Porters, and some don't; some need Bartenders, and some don't).

 

What might be the best way to solve this?

 

Thank you for any guidance you can offer.

 

UPDATE: I am trying a loop within a loop (cycles through the Template data until all the positions are accounted for and generated). It seems to be working. But if you have some strong reservations about this method, please let me know.