I am quite comfortable in programming in SAS. SAS has a first and last function, I wonder if FileMakerPro has something similar.

 

CustomerID, InvoiceNo, ItemNo

1001   5525   1001

1001   5525   1004

1001   5525   1007

1098   5526   2001

2001   5527   3501

 

Expected result is :

 

 

CustomerID, InvoiceNo, ItemNo, CostItem

1001   5525   1001   25$

1001   5525   1004   10$

1001   5525   1007   10$

1098   5526   2001   25$

2001   5527   3501   25$

 

 

Given the above table I would like to know if this is possible to do the following calculation in FileMaker Pro.

 

Set verifID to concatenate(CustomerID & InvoiceNo)

 

Sort on verifID ascending

 

if first.verifID and last.verifID then set Costitem to 25$

else if first.verifID then CostItem to 25$

        else CostItem to 10$

 

After completing I will do a sum calculation using verifID.

 

1001 5525 total = 45.00$

1098 5526 total = 25.00$

2001 5527 total = 25.00$

 

With regards!

Daniel

It seems that your goal is to calc an Invoice subtotal.

 

Invoice::Subtotal_c = sum ( invoice_LineItems::extension)

 

where

LineItems::extension = Qty * UnitCost

If this is supposed to be based on a found sets of line items, you could probably use something like =

 

Case ( InvoiceNo = GetNthhRecord ( InvoiceNo ; Get (RecordNumber) - 1 ) ; 10 ; 25 )

 

I believe the prices should be stored in fields and records, rather than hard-coded into a calc, though.

 

 

 

Another possibility is to count the items, multiply the sub-summary count by InvoiceNo times 10 and add 15.

Thanks for the quick response.

 

In the final calculation the prices will not be hardcoded. The reason it has not been done at the moment is because I want to focus on a look a like first/last function.

 

In the end the customer invoice will a list of all lines with their appropriate CostItem.

 

The multiply the sub-summary count by InvoiceNo times 10 and add 15 would give me the total amount which I will need later on. However, the difficulty I do have at the moment is to calculate the right cost for each of the items.

 

I will look further the GetNthhRecord function.

 

Daniel

Many thanks Comment!

 

The result of the GetNthRecord function is exactly what I was looking for.

 

I will try to understand better the combination of the GetNthhRecord ( InvoiceNo ; Get (RecordNumber) - 1 ) functions.

I am still not sure what exactly is being calculated here. I suspect that basing it on the presence and position of an item in the found set may not be robust enough. Why don't you use a relationship to count the invoice's items?