If this AND that....

[DeborahW]

I'm just starting out with calculations and have made some really simple ones that work (!) but I think what I want to do now is beyond what my mind wants to get around at this point.

I have a table with records that are created through a portal in another table (basically a line items arrangement). I need to do a calculation to add all the quantities of items (fabrics) that meet two conditions from TWO fields in the LI table. So, there are:

ShippedTo (a location)

MetersShipped (a number)

I want a calculation that says IF ShippedTo = (a particular location) AND if the FabricID for the record is the same (there can be multiple shipments to the same location so multiple records in the LI table), then I get a sum of the MetersShipped to that location. I want to display this total within the LI table (in Form View) and also use it for another calculation in another related table. This calculation would say that IF the total of meters USED (also a calculated result) in the related table exceeds the total shipped to a given location then some sort of warning will ensue (haven't decided what yet, but having the text color of the meters USED change color would be cool - no idea how to do this). A simple text field that would return some sort of text warning would be OK.

Any help would be appreciated.

BR,

Deborah

[Brian C]

I believe that what you are trying to do can be accomplished by creating a calculation field which combines the 2 fields together.

If you want to be able to only total the items together that belong to a particular shipment, you would also need to add your record ID to this calculation.

Example Data:

ShippedTo = "107" (the id number of the location shipped to)

FabricID = "304" (the id number of the fabric shipped)

InvoiceID = "0000000001"

From the above you could then construct a calculation field to contain the following:

Field Name: cShippedFabric

Field Calculation: ShippedTo & " " & FabricID

You can then create a relationship that links to this calculation field to create totals of only the records that share the same ShippedTo and FabricID.

If you want to further limit this to a particular order as opposed to every record in the file that shares the above criteria you can instead use the following:

Field Name: cInvoiceShippedFabric

Field Calculation: InvoiceID & " " & ShippedTo & " " & FabricID

In order to total the items all you need is a calculation field to sum the total metersshipped.

calculation = sum(Relationship_Name::MetersShipped)

[-Queue-]

In 7, you do not need concatenated calculation fields for a multiple-criteria relationship. You can simply match up multiple fields in the relationship definition. Do this by matching two fields, clicking Add, matching two more fields, clicking Add, and so forth, as is necessary.

[Brian C]

Good to know!

I assume this solves the issue with FMPv6 word length and total length restriction in a key.

(Hense the use of spaces in my concatenated keys for compatability with the length restrictions and ease of parsing/readability)

Is there a practical limit to the number of fields that you can add in this way in v7?

[DeborahW]

Thank you. I got the first part done with a self-join (both fields were in the same table). Works like a charm so far. Tomorrow I'll try to find time to do the other parts.

Deborah

[-Queue-]

Brian, I am unaware of a limit on the number of criteria per relationship. Perhaps someone else has such information?