9 Replies Latest reply on Aug 19, 2016 9:49 AM by sawhat

    Schema Help


      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






        • 1. Re: Schema Help

          You should think about what happens to orders over time..

          Do you really want to change the value of an order once it has been shipped and paid for, which is what will happen with the current schema if the misc taxable charges are changed.

          • 2. Re: Schema Help

            I have a calculated field in "Order Lines" that gets the current value from Misc_taxable_charges. Wont that prevent it from overwriting a new value?

            • 3. Re: Schema Help

              If its an unstored calculation in order_lines it will always retrieve the current value from Misc_taxable_charges.

              The best choice is a scripted process that puts the actual value from Misc_taxable_charges into a standard field (not calculated in any way shape or form) in order lines at the time the order line item is added.


              auto enter calcs and lookups are prone to errors if you dont understand and plan for every event that can cause the field to refresh

              1 of 1 people found this helpful
              • 4. Re: Schema Help

                Order Lines and Orders are perhaps better named in this case to Work Orders and work order lines. That said they must be mutable. I can see in the case of an invoice where you would want it to be immutable.


                That said what would be a good way to create immutable invoices once they are closed?

                • 5. Re: Schema Help

                  I used record level access control. I set a value in the parent record that indicated that the invoice had been printed. Then "limited access" calculations in both the parent record and the related line items table denied editing if that status field in the parent record had a value that indicated that it was printed.


                  I then needed to take one extra step to prevent creating new line items for a 'printed' invoice, but this can be done with a validation calculation in the line items table that rejects all input if the parent table is 'printed'.

                  1 of 1 people found this helpful
                  • 6. Re: Schema Help

                    Sounds Good! I have a status field in the Parent Table "Orders"


                    I'll have to read up on limiting access.



                    Thank you for your insight!



                    • 7. Re: Schema Help

                      Why so many nested IFs? Will this not do the same?



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

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

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

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

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

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

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

                      moth_eradicate = "Yes" ; 3 ;

                      moth_treatment = "Yes" ; .5 ;

                      scotch gard = "Yes" ; .4 ;

                      deodorize = "Yes" ; .5;


                      ) * area

                      • 8. Re: Schema Help

                        That is certainly true. I discovered Case() after creating this test



                        • 9. Re: Schema Help

                          I abandoned this method in favor of another method. I guess in retrospect

                          this would have worked dynamically if I had used a SQL select / where in

                          leu of the static price.


                          On Fri, Aug 19, 2016 at 12:46 PM Doug Hauenstein <doug.hauenstein@gmail.com>