Calculation based on a first and last record of a found set

[DPaquin]

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

[bcooney]

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

 

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

 

where

LineItems::extension = Qty * UnitCost

[comment]

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.

[DPaquin]

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

[DPaquin]

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.

[comment]

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?