Progressive count of records sharing the same invoice_id

[Ed Bradley]

Thought this would be simple, but I'm stuck. Thanks for your help!

I need to give the first number of items, say 2, at no cost.

I have that number(2) in a field "no_free". The result should be that the price will be 0.00 for items one and two and the next item, no matter how many more, will be at regular price. What I am trying to do is:

free_or_not calculation field is defined as

If(the progressive record count w/the same invoice_id ≤ no_free; 0; 1)

In the price calculation this field results "free_or_not" is used as a multiplier that keeps or zeros the price.

I need to know how to come up with the progressive record count of items sharing the same invoice.

Thanks,

[comment]

I need to give the first number of items, say 2, at no cost.

First in what order?

[Ed Bradley]

No particular order, as entered. I am trying to stay away from a script.

[comment]

Well, "as entered" IS a particular order... :

Try defining a self-join of the LineItems table as:

LineItems::InvoiceID = LineItems 2::InvoiceID

AND

LineItems::LineItemID > LineItems 2::LineItemID

Then you can use the expression =

Count ( LineItems 2::InvoiceID ) + 1

to determine a line item's ordinal number within the invoice.

Another option is to leave the line items as they are, and apply a discount that equals the price of the first N items at the invoice level.

[Ed Bradley]

Your right : I give it a shot. Thanks so much for your quick response.

[Ed Bradley]

It worked except the first record did not show its number (1).

So I changed the > to ≥ in the relationship and took off the +1 in the field calculation.

It works great, again thanks a million!

Ed