10 Replies Latest reply on Oct 29, 2010 10:14 AM by philmodjunk

    help with script to total amounts based on multiple fields

    kilbot

      Title

      help with script to total amounts based on multiple fields

      Post

      Okay here is thge info and I'm really confused in trying to make this happen.

      this is where I am at with this

      FOR THE PRIME

      If (eval::prime_gender="F" AND eval::prime_cert="udbe" AND eval::prime_ethn="asian"; prime_amount)

      FOR THE SUB

      if (dbe::ethn="asian" AND dbe::gender="F";bid_quote)

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

      I want to add these together and if one does not meet the criteria and the other does I still want the dollar amount from the one that met the criteria.

      appreciate any help

        • 1. Re: help with script to total amounts based on multiple fields
          philmodjunk

          If (eval::prime_gender="F" AND eval::prime_cert="udbe" AND eval::prime_ethn="asian"; prime_amount) + if (dbe::ethn="asian" AND dbe::gender="F";bid_quote)

          Adds them together (If these are defined in different calculation fields, you can just add the fields). If only one is valid, you'll get only that amount returned as the other result will be null.

          That all seems too easy though. Is there something I missed or didn't understand?

          • 2. Re: help with script to total amounts based on multiple fields
            kilbot

            I will try and see maybe I have been just looking at the screen to long.

            • 3. Re: help with script to total amounts based on multiple fields
              kilbot

              Okay it must be something else I'm not getting so here is the full senario:

              I have a portal that shows sub contractors that I'm opening in a list view so I can print it out for a report. I'm trying to add the dollars bid of the sub contractor if they are asian with the prime contractor if they are asian. If only one is asain then I want only the dollar amount from the prime or sub that is asian.

              I made the calculation above and placed it both into the sub summary, trailing summary and body to see if it would work. It works in the body but it only adds the one sub and not all the subs listed that are asian with the prime. I'm trying to get a total for at last page of the report.

              • 4. Re: help with script to total amounts based on multiple fields
                philmodjunk

                What do you want if both Prime and Sub are asian?

                To get the sum of all asian sub contractors, you can use the sum function.

                If your calculation field, cAsianBid, is defined in the portal table as: if (dbe::ethn="asian" AND dbe::gender="F";bid_quote)

                Then Sum ( PortalTableOccurrence::cAsianBid ) will compute the total of all asian sub contractor bids.

                You can also make this a selectable calculation where you can select an ethnicity from a drop down and use that selected value to compute the total for all related sub contractor records of that ethnicity.

                You could also create a list view layout based on the sub contractors table with summary fields to compute this total. Data on the Prime contractor, can be added to this layout and placed in a header or sub summary part. With this type of report, you can see the total for a selected ethnicity, or compute subtotals where the sub contractors for a given prime contractor have been grouped by ethnicity.

                • 5. Re: help with script to total amounts based on multiple fields
                  kilbot

                  How and I make this calculation only count once if the contract number is the same?

                  If (evaluation::prime_cert="udbe" AND evaluation::prime_ethnicity="asian" AND evaluation::prime_gender="M"; evaluation::prime_amount; 0)

                  I have only 1 prime listed on each contract and a ton of subs so when in list view it wants to count the prime amout for everytime it list a sub on a contract.: 1 contract 1 prime 5 subs = the same prime counted 5 times

                  • 6. Re: help with script to total amounts based on multiple fields
                    philmodjunk

                    I assume you have two tables. Table 1 documents the Prime contractor ( and possibly the contract). Table 2 documents the sub-contractors so that you can display them in a portal.

                    Define your calculation for the prime contractor in the first table where you should only have one such record--not in the portal's table.

                    • 7. Re: help with script to total amounts based on multiple fields
                      kilbot

                      okay did that and it given me the right amounts when I'm in the table where the prime field were created but when I place it into the table that I have the list of subs it totals 0

                      • 8. Re: help with script to total amounts based on multiple fields
                        kilbot

                        it's working now when I place the prime field in the footer instead of the trailing summary. Why is that?

                        • 10. Re: help with script to total amounts based on multiple fields
                          philmodjunk

                          The value you get will be determined by your relationship and whatever record is "current" at the time you look at the value in your trailing sumary or footer. When you print or preview the layout, FileMaker evaluates the field as though your first record in the found set is current.

                          If you have multiple records present in your found set of Sub contractor records that relate to different Contract records, you should place this field in a sub summary part when sorted by Contracts::ContractID (subsitute your table Occurrence and field names), so that it is set up as a sub head showing the relevant info for each contract when you sort your records to group them by contract ID.