9 Replies Latest reply on May 7, 2010 1:20 AM by iFM

    how to update/commit records between two portals in the same layout

    iFM

      Title

      how to update/commit records between two portals in the same layout

      Post

      Hi,

       

      I'm using filemaker pro advanced 11 on mac os x and am relatively new to filemaker with a good technical background.

       

      Now I'm trying to develop an inventory and accounting solution for a trading firm.

       

      The firm purchases products from a manufacturer.

      A layout is used to input the firm's purchase document i.e. manufacturer's invoice which has several products in one portal and multiple taxes in a second portal.

       

      The layout shows records from the 'purchase' table (with pk PurchaseID),

      products portal has related records from 'purchaseitems' table (with pk PurchaseID),

      taxes portal has related records from 'purchasetaxes' table (with pk PurchaseID).

       

      The problem I face is, if data in products portal is modified, then the corresponding calculation of taxes is not updated since the tax records are in a different portal whose records are not committed.

       

      How do I commit records in the taxes portal based on changes in the products portal?

       

      Is there a better approach to solve this problem?

      Any help will be appreciated.

       

      Thanks & Regrads.

      :-)

        • 1. Re: how to update/commit records between two portals in the same layout
          iFM

          anybody here? .... pls help

          • 2. Re: how to update/commit records between two portals in the same layout
            mrvodka

            What is the purpose of the tax portal? Shouldnt tax be added to the total or at least each line item?

            • 3. Re: how to update/commit records between two portals in the same layout
              iFM

              Taxes are applied on the gross total amount of the invoice not on each item.

              There are different types of taxes like VAT, Cess, TCS ... all or some of them applicable on the same transaction, duty rates vary between products and cities, that's why the need of a portal.

               

              • 4. Re: how to update/commit records between two portals in the same layout
                mrvodka

                You should be looking up the tax rate from a tax table and then storing the rate into a rate field in your invoice. You dont need a second portal. Again a lookup is what you need.

                • 5. Re: how to update/commit records between two portals in the same layout
                  iFM

                  that was my initial approach, then I was informed that

                  1. a purchase invoice can have 'different combination' of taxes and duties (minimum of 3, max of 6 to 7),

                  2. also there are a set of taxes which are applicable on other taxes (yes, a tax on tax).

                  3. the type of taxes and duties change every year as the country is going through reforms.

                   

                  if I keep a tax rate field in the invoices table or if I use multiple fields to segregate different tax rates, then I lose flexibility to use those fields for a new type of tax.

                  Since it is uncertain how many taxes may be entered in an invoice, I can't rely on having a fixed number of fields for tax rates in one table. 

                  Thats when I added a different table for taxes using the concept of 'lineitems' which gives me flexibility for the number of taxes and also any new type of tax if introduced any time in future.

                  Everything works fine when a new purchase invoice is entered, problem starts when an existing invoice is modified which only commits the purchaseitems table without committing the purchasetaxes table.

                   

                  I hope I've clarified my scenario...

                   

                  • 6. Re: how to update/commit records between two portals in the same layout
                    philmodjunk

                    Actually, a look up table should be very effective for this. One advantage of the table is that as countries change their tax laws, you should be able to update your database simply by entering new data in the look up table.

                     

                    The challenge is to store not only the rate but add enough other fields so that the correct value can be both looked up and also correctly used in the tax calculation. One option is that you can actually store a calculation expression as text in your tax rate table and use the evaluate function with this field to use completely different formulas to compute your tax for different records in your tax rate table.

                    • 7. Re: how to update/commit records between two portals in the same layout
                      iFM

                      I already have a look up table for taxes.

                      As stated before, I've recorded all taxes used in an invoice in another table (say linetaxes conceptually similar to 'lineitmes')

                      My problem is not with the database design or the calculations.

                       

                      Problem is when the rate or quantity in the lineitems table is changed, this changes the gross total of the invoice and therefore should reflect a corresponding update in the linetaxes table.

                      Since the data in the record of lineitems has been changed by the user, filemaker commits the record, but since no data has changed in linetaxes (as it is a calculated field) filemaker doesn't commit this record and hence the linetaxes table reflects old calculations.

                      • 8. Re: how to update/commit records between two portals in the same layout
                        mrvodka

                         


                        iFM wrote:

                        My problem is not with the database design or the calculations.


                         

                        I disagree. I think that is what is causing you your issues.

                         

                        There are still a few things that I do not have clarity on. For example when I asked you earlier if the tax should be on the individual line items, you said:

                         

                         


                        iFM wrote:

                        Taxes are applied on the gross total amount of the invoice not on each item.

                        There are different types of taxes like VAT, Cess, TCS ... all or some of them applicable on the same transaction, duty rates vary between products and cities, that's why the need of a portal.

                         


                        That is by itself is contradictory. If the rate various per different products, then it is on the line item. How could it be just on the total of the invoice?

                         

                         

                         

                        Furthermore, if you want to make it so that a different table is used for adding multiple type of taxes, then you may have to change your linetaxes table to be based on the product.

                         

                        Take for example the following ( very generic):

                        Tax Types

                        1  City - 5%

                        2  County - 2.5%

                        3  VAT - 3%

                         

                         

                        Products Table ( and applicable taxes ):

                        1  Book ( City, VAT )

                        2  Bread ( None )

                        3  Clothes ( VAT )
                        4  PC ( City, County, VAT )

                        5  TV ( City, County, VAT )

                         

                         

                        Well then lets say on an invoice one purchases all the items except the TV.

                        The line items would look something like:

                        pkLineID         fkInvoiceID          fkProductID           qty         price         rate         sub ( qty * price )       tax ( sub * rate )  

                        55                          1                             1                       3            20             .08            60                       4.8

                        56                          1                             2                       2            4                0               8                         0

                        57                          1                             3                       2            10             .03            20                       .6
                        58                          1                             4                       1            1000        .105          1000                  105

                         

                         

                        Total tax would be:    110.40

                         

                         

                         

                         

                         

                         

                         

                         

                         

                         



                        • 9. Re: how to update/commit records between two portals in the same layout
                          iFM

                          Appreciate the help provided here.

                           

                          My problem is resolved using the 'evaluate' function which re-evaluates a calculation when related fields are modified.

                           

                          :smileyhappy: