3 Replies Latest reply on Feb 25, 2015 9:14 PM by philmodjunk

    Having Trouble with script calculation

    RussellCline

      Title

      Having Trouble with script calculation

      Post

      I am pretty new at this and must be missing something simple. I am trying to write a script that to keep a running tab and record the totals in another table. The problem I'm having is I have multiple prices for the same item the items depending on the price code per item on the discount per item. In excel or access I would set up a relationship that worked out when script xyz runs in table A column A line 1 multiply that by table B Column A row 1,2,3, etc a coding to the discount code that was selected. with filemaker I get first part of the equation because I'm working that row currently but on the side that selects a row I see how to describe the table Table B and the column A TableB::A but how do i select which record I want I have a Column labeled ID but were would I add that on?

        • 1. Re: Having Trouble with script calculation
          philmodjunk

          The "alphabet soup" used to label your tables makes your description of what you want to do hard to follow. Real table names are better.

          I am trying to write a script that to keep a running tab and record the totals in another table.

          A "running tab" of what exactly? Records in a table? Presumably, this would be a running total, but could be set up many different ways depending on what you have to use to compute that total and the results that you want.

          And why do you want to record the totals in another table? That is sometimes necessary, but often is not necessary if the only reason is to produce a report by category.

          • 2. Re: Having Trouble with script calculation
            RussellCline

            so basically this is the situation

            a customer purchases fruit apples oranges and bananas each day of the week they call in with an order but the price changes daily we do this with a price code each customers price but at the end of the week we tally all of this up in the past we have done this using access but want to move away from access as we are migrating away from microsoft in general so in the past we used 2  tables that looked like this

            fruit QTY table

            Name       Mon Apples     Mon Oranges     Mon Bananas   Mon Rate    Tues Apple Tues Oranges Tues Bananas Tues Rate .... etc

            Toms deli      10                     1                        13                      a                   10                    4                     5                        c

            Discount table

                 Apple    Oranges    Bananas

            a    1.15       2.15             3.15

            b    .75        .95                2.00

            c   3.15          2.25            3.75

            on top of this we had another table which held customer information and another which held there order information for the week by day

            at the end of the week we tally it up and enter it in instead of making a daily order form so on monday and tuesday a customer might be rate code A while the rest of the week the were b

            so what we did was ran a script  it said monday apples x Apples:mon rate code + tues apples x Tues Apples x Apples: Tues Rate code.. etc

            at the end we have an invoice that says apples $30 oranges $22 Bananas $12 Total $64

            so I get setfield then I tried using case the check whist the rate was and then multiply the apples qty by the apples price but I'm Only defying a field so all of apples I want to be more specific in my calculation and say multiply it by apples field row a? is this making any more sense?

             

            • 3. Re: Having Trouble with script calculation
              philmodjunk

              I'd start by restructuring your basic data model.

              I'd set up a transactions table with these fields:

              _fkOrderID
              _fkProductID
              PriceCode
              Qty
              UnitPrice (Looked up from Price table [see below])
              cCost     (calculation field defined as Qty * UnitPrice

              An Order or Invoice table would have these fields:

              __pkOrderID
              TransDate
              _fkCustomerID
              cOrderTotal (Calculation Field: Sum ( Transactions::cCost )

              A Customer table would have a __pkCustomerID field plus any other fields needed to document a your customers

              You'd have a Price table with:
              _fkProductID
              _fkPriceCode
              Price

              Relationships:

              Customers----<Orders---<Transactions>------Price>-----Products

              Customers::__pkCustomerID = Orders::_fkCustomerID
              Orders::__pkOrderID = Transactions::_fkOrderID
              Price::PriceCode = Transactions::PriceCode AND
              Price::_fkProductID = Transactions::_fkProductID
              Products::__pkProductID = Price::_fkProductID

              For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

              You can now calculate totals in more than one way. A summary field in the Orders table can tally up order totals for one or multiple customers and a summary report can use this same field to produce a variety of sub totals.

              You can also use a summary field in Transactions to get totals with subtotals by _fkProductID as well as order totals. These totals can be for a single customer, or multiple customers--which can be broken down by customer or not as needed.

              For a Tutorial on summary reports, see: Creating Filemaker Pro summary reports--Tutorial