Limiting values in a value list
I have a customer table with four customer types; 1=person, 2=corporation, 3=school, 4=league
when I create a new job I want to look up only certain types of customers…for example if I create a portrait I want a value list that only contains customers with a value of 1 (person). I've created three a calculated number fields in the customer table to attempt to do a self join field _sj_person value = 1, _sj_corporation value = 2, _sj_league value = 4 (schools are not a problem because I have a detail record for each school that I use for all school jobs) I then created 3 table occurrences for the customer table and joined each using the _sj filed as the primary key to the customer type key in the customer table. I then created a value list based on the joined table. I either get all the customer records or nothing depending what I've tried. I think I have over complicated this and now have a mess. I've tried every type of value list I can think of…this should be simple and probably is.
The tables in gray are "real tables" and colored tables are occurrences.
All help is greatly appreciated.