0 Replies Latest reply on Jun 18, 2014 11:03 AM by jbinkert

    summary of portal record items across multiple portals and parent records

    jbinkert

      Hello all,

       

      Although not a overly experienced user of Filemaker ( FM Pro 13 Advanced is my first significant foray into a complex FM dB app development ), I have had successful, but limited application results with earlier versions of filemaker, Omnis, Access, and I do a lot of formulas in Excel with some macro programming.

       

      My approach to developing this particular application is to build and test the relations, scripts, layouts, calculations etc. in pieces to prove the concepts before I launch full bore into the complex system.

      I have downloaded various examples of solutions to other developer queries and worked through them in detail using the script debugger and dataviewer to figure out what is happening behind the curtain.

       

      At the moment, I am quite stymied as to an appropriate way to approach the following:

       

      -----------------------------------------------------------------------------------------------------------------

      PART 1

       

      TOs:

      ACC (accounts)

      CCT (invoice line items)

      TAX (federal, provincial, municipal taxes)

      TAX_ALLO (percent allocaion of TAX rates, applied to each invoice line item charge)

       

       

      There are many ACC

      Each ACC has many CCT

      Each CCT has many TAX, but not all TAX are on each CCT

      TAX_ALLO is used as a one-to-many-to-one join table to select and apply percentage of each tax in TAX based on CCT::taxroute criteria.

       

      for example

       

      ACC1

      has

      CCT 1, 2, 3, 4...n

      CCT1 has TAX_ALLO Tax1 10%, Tax5 90%

      CCT2 has TAX_ALLO Tax2 30%, Tax5 70%

      and so on.

       

      On a CCT layout I use a TAX_ALLO portal to bring in the taxes for each circuit record.

      Each TAX_ALLO portal record has a tax name "Tax1", "Tax2", etc., where the name of the TAX allocation refers to a specificie TAX. It could be Ontario HST, Qubec RST, Federal GST, etc...

       

      so to sum the above example,

       

      ACC1, (sort by ACC)

      add a summary part with summary field for totals of:

      - CCT1 & CCT2 (CCT::PreTax) - ( this works - CCT table summary field "s_PreTax chgs" )

       

      - TAX1 - ( where TAX1 is the TAX1 rate, allocated to 10% of the pretax charge for CCT1 )

      - TAX2 - ( where TAX2 is the TAX2 rate, allocated to 30% of the pretax charge for CCT2 )

      - TAX3 - ( no summary field required for ACC1 invoice)

      - TAX4 - ( no summary field required for ACC1 invoice)

      - TAX5 - ( where TAX5 is the TAX5 rate, allocated to 90% of the pretax charge for CCT1, plus the TAX5 rate allocated to 70% of the pretax amount for CCT2 )

       

      so for ACC1 the summary part should show the sum of fields:

      CCTS

      TAX1

      TAX2

      TAX5

       

      --------------

       

      if I there are say, 9 accounts ACC1....ACC9.

      Then I should also be able to generate additional detailed summary data that shows:

      A list of each account along with each of the summary charges for that account's invoice.

      ACC1 - sum PRETAX, sum TAX1, sum TAX2, sum TAX3(0), sum TAX4 (0), sum TAX5, sum TOTAL charges

      ACC2 -

      ACC3 -

      GRAND SUMMARY all invoices by summary fields.

      sum ACC1 PreTax...ACC9 PreTax9,

      etc...

       

      ------------------------------------------------------------------------------------------------

      PART 2

       

      A CCT, also may have non recurring charges, these are referred to as OCCs

      OCCs on a CCT may have different TAX allocations than the recurring charge tax allocations applicable to a CCT

      This I do and manage using another Table for OCC and it is another portal on the CCT layout. (this calculates and displays okay)

      The OCC table is also connected to the TAX_ALLO table

       

      So a CCT line item, may have recurring charges (PRE_TAX_AMT) and non-recurring charges (OCC) and possibly differing allocations of taxes to each

      The CCT charges for an invoice will have a PRETAX TOTAL + OCC total + a sum of TAX1, +a sum of TAX2, etc., as TAXn is applicable.

       

      For a given CCT record, I can not seem to figure out how to reliably add TAX5 allocated to the PRE_TAX, to the TAX 5 allocated to the CCT OCC in order to get a SUM of TAX5, for the CCT record.

      (ie TAX5 on an OCC may not be added to TAX1 on the CCT PreTax_

       

      The OCC for the CCT might have only TAX5

      whereas the PRE_TAX recurring charge for the same CCT might have TAX1, TAX2, TAX5.

      The allocation of theTAX5 amounts must be correctly summed for the CCT record, and then the SUM of thes line item summary details is further summed for the ACC invoice.

       

      ___________________________________________________________

       

      I have been searching for training materials on advanced scripting and reporting techniques in order to solve this challenge but the Lynda.com site was too basic for my needs.

      I worked through the tutorials in the Missing Manual series for FMP 11, and although I felt it was a great set of tutorials it wasn't enough.

      I would really appreciate it if I could learn the theory behind how to approach this problem... I feel that I must be missing something rather fundamental and am waiting on the "Aha!" moment...

       

      Any suggestions for approach, solutions, examples, learning materials... all welcome.

      And if I'm trying to do something that's just not doable, now would be a good time for me to learn that too....

       

      Thank you all.