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?
I will try and see maybe I have been just looking at the screen to long.
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.
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.
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
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.
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
it's working now when I place the prime field in the footer instead of the trailing summary. Why is that?
sorry no it doesnt
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.