Calculations with repeating fields

[slytle]

I am working on the invoicing section of my Order Entry/Scheduling/Estimating/Invoicing system.

The way I was setting it up was to have:

a quantity_billed field with 20 reps

a description_billed field with 20 reps and a value list

a unit_price_billed calculation field with 20 reps

a price_billed calculation field with 20 reps

The unit price field is set up as:

If (description_billed = "product1" ; price_list::product1_price ; price_list::product2_price)

The price list is in another table with all global fields.

My problem is that it will not calculate the unit price beyond the first rep. I have tried putting the unit price fields in the same table and that does not work. If I use a number instead of a separate list, then it does work, ie. If (description_billed = "product1" ; 1 ; 2) It shows the unit price of product1 as $1 and product2 as $2.

The price billed calculation works fine.

Anyone have any idea what is wrong? Or if there is a better way for me to go about this?

Thanks,

.sly

[Matthew F]

For this to work I think that price list also needs to be comprised of repeating fields. (You'll need to get the same price into each of the repetitions). Also the price list needs to be in the same table, but not necessarily in the same layout.

Non-repeating fields would work fine, but it would mean creating more field definition in order to have 20 instances on the same record. Alternatively you could put a single non-repeating price calculation in each record and group the records by giving them a single invoice number. A self-join relationship would allow you to view all of the items from a single invoice in a portal. You can only display a portal vertically, not horizontally like you can with repeating fields. Use a relationship between the price list and the current table using the product description as the match field. Then the price_billed is simple to calculate:

price_billed = calculation (quantity_billed * PriceList::unit_price).

[slytle]

Hmmm... Each line item on the invoice as a separate record grouped together by the relationship of an invoice number. That's it! That'll allow the user to add as many line items as they need. I'll be able to use this for the packing slip as well!

Thanks for your input!

.sly