Using a Script to change value in a field with repetition.

[SCbill]

Hello Everyone,

I'm new to scripts and am trying to do something which is probably simple, but I can't make work.

My company uses FM to quote projects. I have created a separate database for pricing per contractor. In the quoting database , the user has to pick which contractor they are quoting. Then when the user picks a labor item, the price for that item is found using an auto-calculated result using an IF statement. This is how it is set up, I'm sure there is a better way but it works now if you pick the contractor before adding the labor items.

QUOTE::Cash Labor=Lookup(PRICING::Cash Cost)*

QUOTE::Smith Contracting Labor=Lookup(PRICING::Smith Cost)*

QUOTE::ABC Contractors=Lookup(PRICING::ABC cost)*

*These are stored as numbers using the looked-up value, not calculations.

QUOTE::Labor Cost= If(Contractor (a text field) = "Cash"; Cash Labor; if (Contractor = "Smith Contracting" ; Smith Contracting Labor...etc*

*This is set up as number type with the Auto-enter Calculation replaces current value so that we can change the costs for special circumstances

Because we can quote multiple labor items, I have LABOR ITEM (TEXT, looked-up value from PRICING) set up as a field with 25 repetitions and LABOR COST is set up to do 25 repetitions.

My problem is that if you have done a quote and change the contractor it only changes the first value of LABOR COST. I also have INSTALL COST and MATERIAL COST (fields with repetition) and it only changes their first value as well.

I was hoping by using a script that starts upon exit of CONTRACTOR that uses the set field I could change all the values.

Any advice?

[Ron Cates]

I would suggest going with a related table of records rather than repeating fields. Many developers will tell you that repeating fields should not be used for actual data. There are some interface techniques and other special cases where repeating fields are appropriate but not for data storage. My understanding is that repeating fields are left over from a time when there weren't related tables. Now if you have multiple instances of data it is better to structure a seperate table where each record would represent a repetition. Then you can display or even ad records through a portal. This issue is probably just the first of many you will end up encountering with repeating fields.

[SCbill]

Yeah, that seems to be something I'm seeing a lot. I inherited this database and the creator apparently thought repeated fields were great. I learned Filemaker through trial and error by looking at his database. Could you get me started or point me to a way to not use them? Currently the database is set up with Labor Items (with 6 repetitions). You can click on it and it pulls values from another database called labor items. I'm starting from scratch and have created a new DB with different tables for Labor, Install, Inventory, and Quoting (instead of having everything set up in its own DB). How would I go about setting up a layout in Quoting where I can click to add labor items (from the table, Labor) and have the price be dependent on what customer is chosen in Quoting? I have my Labor table set up so each possible labor item is listed with a retail and a contractor cost and each labor item is it's own record. I know how to make a portal show records from another table, but I don't know how to integrate them with the current table...if that makes sense. I've started working with the addremovechild.zip file that's floating around this site which is perfect for combining my Quoting table with my Inventory table. The part where I think I'll run into a wall is coming up with a total price based off of the labor and install items picked for each quote. Any help or guidance or simply pointing me to a good starting place would be appreciated.

[comment]

See if this helps:

http://fmforums.com/forum/showpost.php?post/309136/

[SCbill]

Yep, thanks again. I love this website, it is saving my butt.

[SCbill]

I got that to work so that I am using a portal to add line items instead of fields with repetition. The thing now is that if I change contractor it deletes the line items instead of just changing the costs. Any suggestions? I added contractor to the LineItems table and made a relationship between the Contractor in Invoices and LineItems add when I re-add the line items it does the adjusted costs.

[Ron Cates]

Are you using auto enter serial numbers as primary keys or are you using something like a name? All tables as a general rule should have an auto enter serial number field and that is what should be used for relationships linked to a corosponding foriegn key field. Anything else will cause you loads of problems when the data changes.

[SCbill]

I am using an auto enter serial number for the proposal numbers and the line items. Contractor is a text field. Also, it doesn't delete the records, it simply doesn't show them because the contractor for that certain proposal number then doesn't much where I've created that line item. If I change the contractor back it shows the old line items. I'm thinking I made a connection I didn't need as I type this out. If I get rid of the contractor in the line items, it won't matter what the contractor is...

[SCbill]

Nevermind. Now when I change the contractor it doesn't do anything so I am back to where I started, but at least I'm not using repeated fields. To recap, I'm now using portals to add line items to my quoting system. Now is there some script I could run upon exiting CONTRACTOR that will reevaluate the line items?

[SCbill]

I've got it set up now so that it works because I made the item costs a calculation (to determine who the contractor is so it pulls the correct pricing) instead of a number with an auto-calculated result. Now it automatically changes the price of the line items based off of the contractor, but I obviously can't modify the costs because it is showing a calculated result. Unfortunately our pricing structure isn't set in stone and we do adjust on individual quotes. I know I could set up a script that would replace the result of the Item Cost (number) with the Item Cost (calculation), but I don't know of a way for that script to trigger. Ideally it would trigger when the result of Item Cost (calculation) changes. The problem I see with triggering the script is that the user doesn't manually change that calculated result it simply changes because a variable in the formula changed value. It seems to work for the top most displayed line item in the portal however. I don't know why it would change one but not all of them....

[comment]

Prices need to be looked up (have a look at the demo above again). This "copies" the price into line items so that (a) you can modify it manually and ( it won't change when the pricelist is updated.

If you have different prices for different contractors, you need to look up the price through a relationship that matches on both ProductID and ContractorID.

[SCbill]

Conclusion on this issue. I wasn't sure how to set it up so that the look up worked with so many contractors but really only two price lists. I used a number with an auto-calc that basically uses a CASE statement to find the right pricing. It doesn't change when I pick a new contractor, but I realized that we almost never do that anyway. With using a portal to add line items, we can't just copy a record anyway. The whole reason I wanted this was so that I could copy an existing quote and then just change the contractor.