AnsweredAssumed Answered

Schema Help

Question asked by sawhat on Aug 17, 2016
Latest reply on Aug 19, 2016 by sawhat

I need some help with a best practice for my give case.


My application is basically an Invoice application patterned loosely to "John Osbornes Intermediate Filemaker 12" video.


I have a Layout "Order Form" based on Table "Orders".

On this Layout I have a Portal showing records based on Table "Order Line"

Order Line is populated from Table "Services Cleaning" by key field "_kp_service_id"


I need to apply optional services from Table "Misc_Taxable_Charges" and or Table "Services Treatment" to each "Order Line"

My thought is to use lists constructed from Tables "Misc_Taxable_Charges" and  "Services Treatment" to either lookup or calculate the price and descriptions and copy them to a related field in "Order Line" but I can't figure out how to make that work.


The solution I am using works but is static. I need a dynamic solution so when prices change in Tables "Misc_Taxable_Charges" and "Services Treatment"  they are reflected in "Order Line" calculated fields.


This is what I have now:


I created text Fields in "Orders Line" which represent the fields I have in Tables "Misc_Taxable_Charges" and "Services Treatment". On the Layout these fields are displayed as Radio Button Sets which use a list "Logical Values" to populate them with a "Yes" or "No".


Then I calculate the line price with a static formula:


If( deodorize = "Yes"    and scotch gard = "Yes" and moth_eradicate = "Yes" ; 3.9 ;

If( deodorize = "Yes"    and scotch gard = "Yes" and moth_treatment = "Yes" ; 1.4 ;

If( deodorize = "Yes"    and moth_eradicate = "Yes" ; 3.5 ;

If( scotch gard = "Yes"  and moth_eradicate = "Yes" ; 3.4 ;

If( deodorize = "Yes"    and moth_treatment = "Yes" ; 1 ;

If( scotch gard = "Yes"  and moth_treatment = "Yes" ; .9 ;

If( deodorize = "Yes"    and scotch gard = "Yes"; .9 ;

If( moth_eradicate = "Yes" ; 3 ;

If( moth_treatment = "Yes" ; .5 ;

If( scotch gard = "Yes" ; .4 ;

If( deodorize = "Yes" ; .5;0)))))))))))* area


My thoughts are I need to do 1 of 2 things.


1) Change the Numbers in the formula to references

2) Redesign the Schema