PDA

View Full Version : return range in a field


ABC123

samed
03-02-2007, 03:21 AM
Alphasports sample has invoice header and invoice items. Now imagine that invoice items has additional field called "code". This code in my case describes a group in which a product belongs to. When I make an invoice it contains various "codes" depending how many different products in different groups are used.
How can I get a field also called "code" in invoice header to get a range of codes used in invoice items on that particular invoice.
Example: I used products from groups 5,6,7,8 and I want invoice header "code" field to display it as range 5-8 value.
If this is not possible tell me if you can think of ANY way to get first and last value displayed.

Thanks!

Cian Chambliss
03-02-2007, 07:58 AM
One question - does the code have any associated data (i.e. like a description - i.e. "code 4 means 'tax free'"). If this is the case, I would imagine that a table should be added to the database called 'codes' which has a unique key of 'code', and whatever associated fields - then a table lookup can be added, and management of the codes table can be easily by using vendor as a model.

Stan Mathews
03-02-2007, 08:43 AM
Alphasports sample has invoice header and invoice items. Now imagine that invoice items has additional field called "code". This code in my case describes a group in which a product belongs to. When I make an invoice it contains various "codes" depending how many different products in different groups are used.
How can I get a field also called "code" in invoice header to get a range of codes used in invoice items on that particular invoice.
Example: I used products from groups 5,6,7,8 and I want invoice header "code" field to display it as range 5-8 value.
If this is not possible tell me if you can think of ANY way to get first and last value displayed.

Thanks!

Not sure why you want this stored in the header table. If you want to see it on an invoice

create a calculated field mincode = minimum(INVOICE_ITEMS->code,grp->Invoice_Number)
create a calculated field maxcode = maximum(INVOICE_ITEMS->code,grp->Invoice_Number)
create a calculated field rang = calc->mincode + "--" +calc->maxcode

Put the rang calculated field on the report.

I think you could use the same calculated fields on a form though the maximum() and minimum() functions are recommended for report use only. The same calculations can be performed with the tablemax() and tablemin() functions.


I would avoid storing this data in a header table field. Just display it when you need it.

samed
03-02-2007, 09:10 AM
I think that will do the trick Stan, will let you know after I try it, thanks! Cian there is no associated data with that code, just need to display it.
Hope I will get this thing working, if not I will cry some more.

Thanks again.

Samed