Value list based on two related tables?

[Matthew F]

I'd like to create a value list for a popup that displays values from the combination of two related tables, not just one. Any ideas on how to go about this?

Matt

[aaa]

See attachment.

T1.zip

[Søren Dyhr]

See attachment.

Carefull - carefull a calc containing ValueListItems( is bound to be unstored in order to reflect changes. But Making it value source to a valuelist does it index/store/freeze much more than you wish for!

This is why I change the popup into a "Choose from List" in Applescript

set aVar to second item of cells of table 1

set oldDelimiters to AppleScript's text item delimiters

set AppleScript's text item delimiters to return

choose from list (text items of aVar)

if result not is false then

	set cell "theReciverField" to result

end if

set AppleScript's text item delimiters to oldDelimiters

--sd

[Matthew F]

aaa,

My tables are related a little differently. Actually I have one table with two additional related tables using match fields for a common ID number. Your solution works fine, with a little rearrangment as shown in the attachment. In this example fields from two related tables are combined to give the value list for a field in a third table.

Thanks again!

[Søren Dyhr]

Your solution works fine, with a little rearrangment as shown in the attachment. In this example fields from two related tables are combined to give the value list for a field in a third table.

I'm afraid not, try to add an significant different entry in one of you tables ...and watch it mising from the popuplist. There is no point in dynamic valulists if they won't freshen and reflect changes ...if it isn't a problem would I hardwire one single VL instead of defining 3 of them.

--sd

[Matthew F]

Hmm. Maybe I'm missing something here. I've deleted the superfluous fields to make it clear where the data entry is supposed to go.... and it refreshes instantly for me. I must admit, though, its not always clear to me when it is better to index or not index a field.

P.S. I've removed the attachment here and added it as a .zip file to the Tips forum.

[aaa]

I have not *.sit archivator. Therefore i can not read your attachment.Can you attach in .zip format.

[Søren Dyhr]

Thats a very clever way of using the bidirectionality of relations, that I haven't thought of yet...

I played along with it, and it seems like you can make an arbitrary table and relate it with the cartesian product, and the just Ignore the warning about the indexing matter ... wich reminds me of the findings that Kevin Frank and Debi Fuchs once made on relations with globals as keyfields in both ends.

Actually do I find you idea, so good that you ought to convert it to a tipfile! But it could be daft ignorance on my behalf??

--sd

[onefish]

This is very clever indeed. Any thoughts on how I may be able to omitt values from value list entirely, that appear in both tables? Essentially, value list used in table 2 will use values from table 1 and omit all values that allready exist in table 2.

[Matthew F]

Hey aaa,

Thanks for helping solve this dilemma. As suggested I've added this to the tips forum. You can find it at this link.

Cheers,

MF

P.S. Stuffit expander is free.

[Lee Smith]

Hi MF,

Stuffit expander is free.

While this is true, for some reason our friends with widows operating systems have problems with .sit format, even with free Stuffit Expander.

I found that it saves me time if I just zip everything in the first place.

HTH

Lee

[aaa]

I little changed your solution and see what i got. It is really good idea.

T1copy.zip

[Søren Dyhr]

Any thoughts on how I may be able to omitt values from value list entirely, that appear in both tables?

Look here:

http://www.clevelandconsulting.com/support/viewtopic.php?t=120

The good thing is that John Sindelairs suggestion isn't recursive ...so it won't be needing 7Dev!

--sd

[Matthew F]

Yes, changing the one or both of the value lists for the individual fields to "include all records" is another option which might be widely applicable.

In the attachment to the Tips post I also changed the List_of-all field definition to include the Get(FileName) function in place of "T1 copy" to allow the user to change the name of the file.

[Matthew F]

Any thoughts on how I may be able to omitt values from value list entirely, that appear in both tables? Essentially, value list used in table 2 will use values from table 1 and omit all values that allready exist in table 2.

You could apply the FilterValues function to find the intersection between two different value lists. Unfortunately this does not omit items of one value list from a second, as you requested. Attached is the previous example reworked to include of a Popup which include the union of to fields as well as a second Popup which is the intersection of the two fields. In these examples the "Include all values" option was chosen in the VL1 and VL2 value lists so the items are not confined to the current record.

IntersectVL.zip