Can't create a VL

[gaby78]

I have a LineItems table linked to Inventory table (ItemRefNbr=ItemRefNbr). In Inventory table I have a Status field (calc field) with the following formula: Case(not IsValid(LINEITEMS::ItemRefNbr);ItemRefNbr).

The Status field shows ItemNbr only for Items not sold yet. When I try to create a value list on the Status field (to show unsold items only), FM won't let me. It keeps displaying the same message "This value list will not work because the field cannot be indexed".

I need to create a VL that shows only unsold items.

Thanks

[Søren Dyhr]

Yes it's going to fail because the calcfield never gets stored, due to the related values it's based on.

Gather the sold items ID's by ValuelistItems( on the primary side of the relation, where it easily can be unstored, and use it as keyfields in a relation to an extra TO of Items.

The key to this is non equi join the unequal relation type.

--sd

[gaby78]

Hi Soren:

Thanks for trying to help.

I am a bit confused; could you please clarify the following points:

1) How does the SelectedItem field in ItemLines table get populated? Is it by VL based on Items table? If yes Isn't the VL going to show all items?

2) I don't understand how the Occupied field gets populated. Is it by a second VL based on FreeListBuilder table?

3) On what table is the VL that shows only unsold items created (Items or FreeListBuilder)?

4) Is it the same ItemId that gets inserted in both fields (SelectedItem and Occupied)?

If you have the file that you can post, I would appreciate it very much

Thanks in advance

Edited July 8, 2006 by Guest

[Søren Dyhr]

There are two Valuelists one that's build by the chosen items, in the itemlines which is making it work behind the lines via ValueListItems(, the next is build over the new relation showing the ones not yet taken and is used in the popup the user sees and uses when entering data in a new itemline.

What the ValuelistItems( does is to produce a multiline key to the item table in a new TO connected with the unequal relation type.

--sd

[gaby78]

Hi Soren:

I finally got it to work but manually. As I undestand it Occupied is a multikey field (each itemID sold separated by a carriage return) that grows as items are sold. The only way I can get it to work is to manually copy the new itemID sold in the Occupied field to get a list of sold itemIDs separated by carriage return. If I stick a VL of sold items on this field, the VL inserts a single value replacing the previously entered value. By what meachnism do you automatically add a new ItemID in a multikey field preserving the previously entered ItemIDs?

[gaby78]

I figured it out. Thanks for your help Soren.