Summarising a line item field into one field

[SteveGriff]

Hi guys

I have two databases, the master database, and the line items database, linked up in the usual master::order_id <> line_items::order_id relationship.

What I need in my master database is a calculation field(?) that loops through related records, and take a field's contents and puts it together.

Example:

four records in my line items db. With a field called product name. the product name field in the four records contain "Pen", "Pencil","Ruler","Eraser".

What I need is my master db calc field to loop through the field and have the contents:

"1) Pen, 2) Pencil, 3) Ruler, 4) Eraser"

Is there anyway of doing this or is it a script job?

Hope this all makes sence

Steve Griff

[Ugo DI LUCA]

Hi,

You want it ranked/sorted ?

[SteveGriff]

I don't need it ranked or sorted.

Just in "order they were entered", which I guess is default

Steve Griff

[Ugo DI LUCA]

Hi again,

Well, no it's not default if you were using the ValueListItem Function....

c_list = ValueListItem(Status(CurrentFieldName, "productList") ustored,

with "productist" is a related value list of all items in the related file.

Then,

c_listcommas = Substitute(c_list, "PP", ",") where PP is a carriage return.

Try to sort the value list by a field in the related file called Record_Number...

[SteveGriff]

Thanxs!

I'll try it out after lunch and reply!

Steve Griff

[Ugo DI LUCA]

Not sure you got it. It won't work

Unless you don't specify a sort key....I was just pointing out this sorting problem.

Another way would be to use a selfjoin to retrieve a rank, and integrate this new field in the value list.

May be using a selfjoin Record_id::Next Record_ID.

Now you could have a look to a recent thread called "Sorting Value List". It's tedious though.

[bradm98]

Create a text field in the master file (e.g. Product_List). Your script should Goto Related Records (show only) and execute a script in the line items file which loops through the related line items, and sets Master::Product_List to:

If(IsEmpty(Master::Product_List), Product_Name, Master::Product_List& ", " & Product_Name).

If you want the products numbered, you'll need to set up a global (number) field in the line items file and increment it as you loop through line item records, adding that number to the Set Field calculation.

I hope my explanation makes sense - I'm sure there are other ways to accomplish this, but I've found this approach works well.