Conditional value list - multiple conditions

[Dave E]

I'm developing a contact database, and I need to track referrals. The default for a contact is no referral, alternatively "referred to" or "referred from". In each of the alternate cases the list of salesmen (...they are all men ) that a contact is referred from or to is the same, so I'd like to use one list for both cases.

I've looked at the "Country" example on conditional value lists, but I can't figure out how to modify it to solve my problem.

Please help....

Thanks

Dave

[oilman]

hi Dave

I am not exactly sure if i understand what you are looking for but here goes. Put your referred to and referred from names in one db and then use that db as your value list.

Lionel

[Dave E]

Thanks for looking at this...

I should have explained that I need to flag whether it is a "To" or "From" referral... so I have 2 fields... the first indicates whether it is "referred to" , "referred from" or "no referral", the second is the salesman employee number. If there is no referral the salesman number must be "00000", if there is a referral in either direction the first field should show "To" or From" and in the second field salesman number must be selected from the list. The default should be "No Referral" and salesman = "00000". If "To" or "From" is selected in the first field "00000" is an invalid salesman code.

dave

[oilman]

Hi dave

Open the program referral and see if this is what you want

Lionel

referral.zip

[Dave E]

Thanks Lionel...

That's pretty much where I had got to... what I'm trying to do is get the same list of salesmen to appear when the referral is flagged as "to" or "by". So far the only way I've been able to do it is to have two records for each salesman - one as a "to" record and one as a "from" record... is there any way that I could do it with one salesman record?

dave

[oilman]

Hi dave

Not sure about getting all one record for "to and "by" but try changing the field format to calculation and look under the function called view value list item.

Lionel

[Ugo DI LUCA]

Why not...

Flag all your salesmen with a n_constant = 1

In the Main File, create a calculation

c_matchToFrom = Case(not is Empty (ToFromField),1,0)

Create a relationship c_matchToFrom::n_constant.

Define a related value list of the Salesman NameField using this relationship.

Attach this list to the "Salesman" field.

Now, when you'd select a To or From, you'd get a list of the Salesmen. If nothing is selected, the field would be empty.

If really you need it to be "0000", then a workaround would be to have a text calculation

c_empty = Case(Is empty(ToFrom Field), "0000",Salesman).

Define it to be not editable, and place it just on front of the Salesman field. Turn this latest transparent.

HTH

[Dave E]

Thanks Ugo

That put me on the right track... I've got it working perfectly! And thanks for your input Lionel.

Regards

dave

[Dave E]

Ugo

Sorry to bug you...I thought I had it licked, but then I realised that it was possible for the user to change the referral to/from field from "To" or "From" to "No referral" without the salesman number updating automatically to "0000". I've tried a variety of validation calculations but I can't get it right. Any thoughts?

dave

[Ugo DI LUCA]

Hi,

I assumed that "To" and "From" were the only 2 values you were entering. Of course, if you enter "No Referral", this won't work as such as the calcs are based on the Is Empty formula.

Change the calcs to these new ones

c_matchToFrom = Case(not is Empty(ToFromField), Case(ToFromField = "No Referral",0,1),0)

c_empty = Case(not is Empty(ToFromField), Case(ToFrom Field = "No Referral", "0000",Salesman)," ")

Or

c_empty = Case(not is Empty(ToFromField), Case(ToFrom Field = "No Referral", "0000",Salesman),"0000") if you want "0000" to be entered and visible even if the referral field IS EMPTY.

Should work now.

[Dave E]

That's it Ugo...

I realised that I had to make the c_matchToFrom field text the same colour as the background and then it displays perfectly!

Thanks a mill...!!

dave