Value list based on two files?

[Jalz]

Hi Guys,

Hoping you can help me out. I have a field in a table called estimates. The field name is CustID. I have attached a value list to CustID, which allows you to select the the CustID and Customer name from the customer file. This works great if I want create an estimate for someone in the customer file.

My problem is, I would also like to create estimates in the prospects file, so in the field CustID in estimates, I would like the abililty to choose ProspectID and ProspectName. Would this be possible?

I know I should have only a sigle file called contacts with both prospects and clients, but unfortunately I haven't so I need to deal with what I have got.

Any help would be much appreciated.

Thanking you in advance.

Jalz

[Ugo DI LUCA]

Hi,

What you'd need at first would be a way to switch from one relationship to the other.

You can't use the ClientID field. Use another calculated field instead and switch its value so that it relates to either the Client or the Prospect File.

One way could be to have a constant index field in Client and Prospect files with say :

c_indexClientKey = "Client" for Client File

c_indexProspectKey = "Prospect" for Prospect File.

Then in the Estimates, create a value list with both values (Client, Prospect) and attach it to a 'n_switchRel' field

Create both relationships and 2 sets of lookup fields.

Then make the name, adress, ..., be calculated checking for not emptyness in the lookup fields.

c_Name = Case(not IsEmpty(ClientNameLookup), ClientNameLookup, not IsEmpty(ProspectNameLookup), ProspectNameLookup,"")

Same for Ids...

I'd add a category field using exactly the same clac, so that you know and can find easily those estimates by customer status.

c_Category = Case(not IsEmpty(ClientNameLookup), "Customer", "Prospect")

Same for Ids...

I think burried in the sample section is a file I've made long time ago that illustrate this process.

Checks for "same value list, different file" or something like..

HTH

[Ugo DI LUCA]

Hey,

I found it. It seems to do exactly what you are after. An old one that could actually be correted a little.

Involves 3 files and an exit script. If you're windows, change the pop-ups to menulists.

Once you have chosen the person from the list, you need to clieck anywhere to refresh the screen. But an exit record could do it in fact.

[Jalz]

Thanks mate,

your solution worked a treat, took a while figuring it out though!

[dbruggmann]

Ugo

Your link to the download file seems to be broken.

[Ugo DI LUCA]

Here it is back, adapted (I hope) for Windows users too...

MultipleVList.zip

[Lee Smith]

Ugo,

By my calculations, it is 3:00 AM there. Go to Bed.

Lee