List of values in one field.

[ominae]

How do you get a list of values in one field. I know can use the List function. But list function list out all in that field. Let say i got a table with 2 fields. Product ID and Serial Number. Taking note that the product id is not unique but the serial number is. I want to create another field which can list all the serial numbers from the particular product id in another table.

Edited October 3, 2006 by Guest

[Inky Phil]

Hello ominae and welcome

If you want to merely display this information then what you want is not a field but a portal. This is a tool for displaying a list of related records in another table.

Set up a relationship between the two tables based on product ID to product ID and use relationship when setting up your portal (see the help file for how to do this)

If for some reason you do wish to get all of this information into one field it can be done via a script. Get back to me if that is the case and I will go through that procedure

HTH

Phil

[ominae]

Thank you. Is there anyway to make the portal invisible? Cos i using some of them just for calculation only.

[Inky Phil]

Hi omin

If you do not wish to display the detail then you do not need the portal and I apologise for sending you off in the wrong direction.

Can you explain in a little more detail what you are trying to achieve with your calculations then we might advise you the best approach.

Phil

[ominae]

ok lol portal is not neccessary. Thanks for telling haha

[ominae]

Ok i have a table and each record which contains a list product ID and the quantity of that product. How do i make another record which calculate the total quantity of the product ID i entered.

Edited October 3, 2006 by Guest
make things clearer

[Inky Phil]

Ok, that is quite easy... Here goes

If you do not already have one define a relationship between the two tables based on RecordID=RecordID.

For my purposes I will call this relationship MyID

Create a calculation field and have it enter the calculation:

Count(MyID::anyfield that is definitely not empty).

Check the 'do not store....box when you are in options.

Now place this field on a layout where the product id field is. When the product ID field changes so will the number in this field as it will always reflect the number of related records

HTH

Phil

[ominae]

Sorry if i phrase it wrongly. What i mean is for example there are 2 records. Record A contains productID xyz which quantity is 10 and Record B contains productID abc which quantity is 20 & productID xyz which quantity is 20. So i wan create something which calculate the total quantity when i enter the Product ID. If i enter productID xyz, i'll get quantity 30 and if i enter productID abc, i'll get 20.

[Inky Phil]

Because it is not clear how you are storing the productID in the current records is there any chance you can post a copy of your file so that I can see your present structure

Phil

[Genx]

I think i understand what you want: the entire quantity of all products with the same product id.

Find out what Table Occurance your layout is based on (i'll call it TO_ProductMain), then go into the relationships window and specify a new table occurance based on the products table (i'll call it TO_ProductSum). Then, add a relationship between the two table occurances using ProductID i.e. "ProductID = ProductID"

Once you've done this, go to your actual table, and add a new field called cProductQuantity -- calculation, result number, evaluate in context of TO_ProductMain. Next for your calculation, simply insert:

Sum(TO_ProductSum::ProductQuantity)

Where the productquantity field stores the quantity in each and every record..

[ominae]

thanks i try first

Edited October 3, 2006 by Guest

[ominae]

But this only work by per record which is i am currently doing actually. Cos each record contains different product ids. This add up all the quantity of that particular record instead of refering to the product id.