Conditional value Lists based on a calc field

[iamthegrey]

Hi Everybody!

Here's today's issue. I have for one of my customers a database with three tables in it. One table is for all of my contacts, and I have three types of contacts, Customers, Vendors, and Employees. When a contact is created one of those three options (Customers, Vendors, and Employees) is selected from a value list to assign a Contact Type to each Contact.

I use that Type in a calculation field along with an auto-entered serial value to assign a Unique ID to each Contact. (the calculation is "Contact Type Code & Contact ID"). For instance my first contact in the lsit is an Employee whose Uinque ID is E1, next record is a vendor (V2), and so on. All is well so far.

Now the next thing I need to do now is create a value list of all contacts whose Uinque ID begins with a V, or a C, or an E. So that I can easily address purchase orders to vendors and thank yous to clients and paychecks to employees without having to scroll through the whole list of clients. Any thoughts. Thanks in advance for all your help!

[QuinTech]

Hi again. Try creating a global text field, which you can set to V, C, or E. Also make a calc text field which is equal to the first letter fo the Unique ID. Relate these two fields and base a value list of the values of ThatRelationship::Name.

You can set the value of the global more elegantly by having a global text field that the user can set to Vendors, Customers, or Employees through a value list, then having a calc field equal to the first letter of THIS field, then using that in the relationship instead.

J

[iamthegrey]

Okay, I admit it. i don't understand global fields. If I do as you suggest and make a global text field with a value list (C, V, or E), doesn't that then make every contact in the list all one type? In other words, isn't the purpose of a global field that it holds the same value across all records.

Thanks Jerry! You rock the hardest!

[QuinTech]

Aw, shucks.

Well, i think you do understand globals, because you're right. But here's the scenario i envision:

You want to send a letter to a customer. You create a new record for the letter or invoice and change the global to C. Then you select the customer ID from the list, thus storing that uniquely (along with the looked-up address, etc.) in this record.

Tomorrow, you want to send something to a vendor. You create a new record for the letter or invoice and change the global to V. Then you select the vewndor ID from the list, thus storing that uniquely (along with the looked-up address, etc.) in this record.

So the global is only a stepping-stone, so to speak, to finding your ID number, and thus its current value is actually irrelevant to the active letter/invoice/record. Right?

J

[iamthegrey]

Jerry,

Okay we are making progress here although I was hoping for a method that would not require any user input ( in your example, changing the global field value ). Any thoughts on this?

[iamthegrey]

Maybe it would help if I rephrased what it is I am looking for. I have one table with all of my contacts in it. Contacts are assigned a type on entry, either Customer, Vendor or Employee. I would like to generate value lists for each type, i.e. I would like to be able to make a value list that contains only customer ID's or only Employee ID's or only Vendor ID's.

Here is what I plan on using them for. When I write up a job order it always has a Customer ID in the Bill To area, so I have no reason to want to display vendors or employees in the pop-up list in the bill to area. This will always be true, so I would like to accomplish it without user input. In other words, when the user goes to the Job Order layout, the database should "know" that only customer ID's need to be displayed in the Bill To area.

[-Queue-]

See attached for an example using the Job Order situation you described.

Contacts.zip

[iamthegrey]

Queue:

Thanks very much! I am going to explore around a little and see if this will work. If I am correct I should be able to take the Customer field and make it invisible to my users, but I am not sure how I would make the vendor and employee variations. I am going to play for a little while and get back to you if I ahve any questions. Thanks again!

[QuinTech]

I see. Well, you could use the same method i described, but take the user input out of it; that is, script navigation between layouts and add to that script a step that sets the global i described to the appropriate letter.

I haven't looked at -Queue-'s solution, but i expect that it is excellent.

J

[iamthegrey]

Jerry & Queue!

I thank you both. I think I can actually use Queue's method but add the scripted navigational step to it help differentiate between the customer, vendor and employee layouts. So a combo solution! Thank you to both of you for your help!

[-Queue-]

Just for clarification, each table would have an auto-enter text field, like the cCustomer one, of Customer, Vendor, or Employee. Then a relationship from this field to the Contacts table would allow you to create a value list for each type, similar to the Customers one in the sample file. Then each layout would use the appropriate value list for the desired field. No scripting would be necessary, unless there were other steps required when switching layouts.

[iamthegrey]

Queue,

I am thinking that I could use the constants from auto-calc field like you mention, or if I have a layout which could be directed to either a customer, vendor or employee I could script it into the button which brings me to the layout from the previous layout. So both your and Jerry's methods would work. Thanks again!