Need To Total (& subtotal) Unique Numbers

[pstamper]

My database is a compilation of line items that create an invoice.

In each line of the invoice I have a field "order number". This field will appear in every line of an invoice. (This means the same number may appear many times in an invoice).

The same "order number" may appear in more than one invoice. (For instance an item may be backordered and subsequently shipped the next day).

I need to be able to total the UNIQUE number of orders (order numbers).

 

[JeanW]

You didn't say if you want those per invoice or per total lineItem, nor if it is the quantity or the amount.


Make a self join relationship on Order Number.
Make a calc : Sum (SjRelationship::Amount) will give you the total amount.
A Count, based upon the ame relationship, will give ou the quantity.

If you want them per invoice, make a combiKey from the invoicenumber and the ordernumber.
Use that value for the self join.

HTH

 

[pstamper]

I am sorry if I am not clear. What I need to calculate is how many orders I am processing. For instance, if an order 5-lines to it, the order number will appear in my database 5-times. I need to calculate this to get an answer of one order.

 

[JeanW]

The Count on Selfjoin on ordernumber will give you the ordernumbers, Count on Selfjoin on combi ordernumber/invoicenumber will give you the ordernumbers per invoice.

 

[pstamper]

Thank you for your help.

I am not familiar with "sefjoin" nor can I find it in filemaker's "help". Can you please point me in the right direction. Thanks you.

 

[JeanW]

There's no standard term for this aspect of relationships. You'll hear the terms self-relationship, self-join and same-file relatiinship.
It merely means that a relatiinship is used to relate the file/table to itself.

Be careful.
When using same-file or self-join relationships, it is important to understand how to use the checkbox options in the edit relationship dialog.
The checkbox allows you to delete related records.
Never check this box when using a selfjoin relationship.
It will create the effectr of cascading deletes and delete all the records in the given file.....