Value list with related values

[Jeff Abraham]

I have a value list that pulls related values from another database based on IDCustomer. If I want to display the same value in every list, regardless of IDCustomer, how can I do it? Sort of creating a general value that all customers can choose.

Example:

Customer 1 Related Value List:

Red

Blue

Green (this value would be in every customer's list)

Customer 2 Related Value List:

Orange

Yellow

Green ((this value would be in every customer's list)

Thanks for the help!

[Ugo DI LUCA]

If this is a "related" value list, then each present and future customer must have at least a record (even blank) with "green".

or

if this list is based on a related value (value 1), create a new field c_value = "green" & "PP" & "value 1" and use this calc for the related value list.

[Jeff Abraham]

Thanks for the reply but I don't understand how that calc works. Let me explain a little more about what I need.

Color profile database:

1. IDCustomer field

2. Color profile field

Job database:

1. IDCustomer field

2. Color profiles available for customer (value list is defined as values from a related field, which is IDCustomer)

Most customers have unique color profiles but I want some color profiles to be available for all customers. How do I create a record in the Color Profiles database that is assigned to all customers, not just one customer? Thanks!

[Ugo DI LUCA]

2. Color profiles available for customer (value list is defined as values from a related field, which is IDCustomer)

I think you mean you are using relation ColorJob = Color Profile:ID Customer::Job Database:ID customer.

Then, when in define value list, you checked "use value from file Color Profile" using ColorJob relationship ! Then you choosed Color Profile as the value.

Then in Color Profile, set a new c_ColorProfile = "Green" & "PP" & Color Profile where PP stands for carriage return.

Back in your value list setting, choose c_ColorProfile as the related value

[Jeff Abraham]

I must be really slow today but how is this going to show all the related values plus colors that I want to appear in every list? You are correct on the relationship based on IDCustomers. What does have a paragraph return do for the value list besides adding a return?

IDCustomer Related Records in Color Profiles:

Blue

Red

Yellow

Need to be related/assigned to all customers, regardless of IDCustomer in Color Profiles:

Green

Orange

Value List Result in Jobs:

Blue

Red

Yellow

Green (in every list regardless of IDCustomer)

Orange (in every list regardless of IDCustomer)

[Ugo DI LUCA]

Carriage return is the way value list are identified in FM Pro.

Create a test file with field 1, field 2 and field 3 and a calc c_carriage = field 1&"PP"& field 2&"PP"&field 3.

Check the result in your calc field ...

[Razumovsky]

Hi -

It looks like you have:

Job database:

1. IDCustomer field contains one value

Color profile database:

1. IDCustomer field contains many values

consider changing

Job database:

1. IDCustomer field to containing 2 values, a generic "wildcard" ID and "pp" and the records unique IDCustomer.

and adding a checkbox field and a calc:

t_checkbox (number-from value list "1")

c_IDCustomer:

case(t_checkbox=1, WildcardID&"PP"&IDCustomer, IDCustomer)

Change your relationship to Jobs:IDCustomer::c_IDCustomer:Colors.

now, for your above example where you want to set the colors that will appear on everyones list that session, click the checkbox for ther records Green and Orange.

you must make sure that the WildcardID is completely unique to all other possible IDCustomer records, and is the same in the JobDB as in the Color calc field.

You also probably want to preserve the original IDCustomer in your JobDB, so create a calcfield for this file as well

ValueListIDCustomer: WildCardID&"PP"&IDCustomer

and make this the origin of your relationship.

I think it will do what you are looking for.

-Raz

[Ugo DI LUCA]

If your colors to be added to the list do change, use Raz solution obvioulsy. Simply add Orange to Green in the calc given otherwise.

[Jeff Abraham]

Hey Raz,

First off, this is exactly what I need: a checkbox to add a color to the value list. Secondly, I have more questions because this is getting beyond my knowledge of calculations/relationships. I'm pretty much of a calc newbie. I created three small test databases for this purpose instead of playing around with a working solution. If you're open to looking at the files I'd appreciate it.

I don't understand the importance of the IDWildcard. Does the IDWildcard have to be in all three databases and how do you create a IDWildcard that is exactly the same? I need to preserve the IDCustomer in the Customer database but I'm lost on the IDWildcard setup and relationship in Jobs.Thanks for the help!

FYI - Ignore the "pp" because that was just acting as a carriage return.

[Ugo DI LUCA]

Hi Raz,

You know from my constant misunderstanding...

but why not just create in Job Database

- t_color profile (value list from Color Profile Database using IDCustomer::IDCustomer and choosing the Color Profile as a match field).

----> a list from all profiles attached to this particular customer.

- t_allcolorprofiles (value list using all records from the Colorprofile field in Color Profile Database) shown as a checkbox.

---->The user can pick the color needed to add from this list of all colors.

-c_newcolorprofile = t_color profile &"pp"& t_allcolorprofiles show as checkbox.

----> Would display the related color for this customer + the new colors selected.

[Razumovsky]

If you're open to looking at the files I'd appreciate it.

Sure, post the files if you are still having problems

I don't understand the importance of the IDWildcard. Does the IDWildcard have to be in all three databases and how do you create a IDWildcard that is exactly the same?

The idea is that the IDwildcard is a value that will be the same in every Jobs ID field(in addition to the jobs unique ID, seperated by a carrige return). Now, when you insert this same WildcardID in the color profiles c_IDCustomer (by using the checkbox to trigger the calculation), the job will be related to all colors with its unique ID in the color profile IDCustomer field, as well as all colors with the checkbox marked.

It could probably just be an "X", assuming that your ID field is text and every ID is at at least 2 or more characters long. So, to preserve your IDCustomer field data, create a calc in Jobs:

c_IDCustomer:

"X"&"PP"&IDCustomer

and a calc in colors:

c_IDCustomer:

case(t_checkbox=1, "X"&"PP"&IDCustomer, IDCustomer)

Now, in jobs, your c_IDCustomer would read:

x (wildcard)

23098gjdf8934h (uniqueCustomerId)

and in colors, your c_IDCustomer would read:

23098gjdf8934h (unique related cutomerID)

78dfs7sd7fgh54(unique related cutomerID)

2457vf0sd4uvf8(unique related cutomerID)

there is no match between these two, so this color would not appear in the list.

But when you clicked the checkbox, it would read:

x (wildcard)

23098gjdf8934h (unique related cutomerID)

78dfs7sd7fgh54(unique related cutomerID)

2457vf0sd4uvf8(unique related cutomerID)

now, both fields share the "X" wildcard value, and so the color would appear on the list.

Hi Ugo-

I am afraid it took me a while to figure out what you were suggesting. I think I get it now, and sounds interesting. The problem is that you cannot mark the checkboxes globaly, so you have to either remark them for each customer, or script a replace or lookup so that the field can be indexed. I am not sure which idea would be better, but it seems both would work. Will have to do some more experimenting with valuelists in calcs. Thanks for the lead.

-Raz

[Ugo DI LUCA]

Your right Raz,

This would produce duplicate. I'm quite curious if there is a way through this with some Substitute(ValueListItems). I'lll tell you...

[Jeff Abraham]

I'm trying to attach the files to this post. Hopefully it will work. Thanks for the help everyone!

These are quick mock-ups of the real database. They are too big to post but these will provide a working example.

relatedlist.zip

[Razumovsky]

Hi Jeff,

Sorry, but I cannot open your attachment. This has happened to me before, so I think it is on my end. I dont have time to troubleshoot the attach. problem now, but let me see if I can give a concise blueprint based on my above posts:

Add these fields to your existing DB's (you do not need to remove any fields or change any data)

Jobs:

c_IDCustomer(calc field, result is text)

"x"&"pp"&

IDCustomer

Colors:

n_checkbox (numberfield) formatted as checkbox using custom value list "1"

c_IDCustomer (calc field, result is text):

case(n_checkbox=1, "x"&"pp"&IDCustomer, IDCustomer)

In Jobs:

create a relationship "colors" that is Jobs:c_IDCustomer::c_IDCustomer:Colors

create a value list that uses all values from related field colors::ColorName.

use this list for choosing the colors in Jobs. It should provide all related colors as well as any for which the checkbox has been marked.

Cheers,

-Raz

[Ugo DI LUCA]

Check this out Raz,

DJ'S SOLUTION

I suddenly came out with the same problem with my customer preferencies list, where I wanted to add-up some new preferencies in another file using only those un-used..., without changing the Main preferencies list.

[Jeff Abraham]

Add these fields to your existing DB's (you do not need to remove any fields or change any data)

Jobs:

c_IDCustomer(calc field, result is text):

"x"&"pp"&

IDCustomer

Colors:

n_checkbox (numberfield) formatted as checkbox using custom value list "1"

c_IDCustomer (calc field, result is text):

case(n_checkbox=1, "x"&"pp"&IDCustomer, IDCustomer)

In Jobs:

create a relationship "colors" that is Jobs:c_IDCustomer::c_IDCustomer:Colors

create a value list that uses all values from related field colors::ColorName.

Raz,

I've added the above fields to my databases but still no luck. I get a "no values defined" in the value list in Jobs. I'm just not getting this at all. What does the "x" and "pp" stand for? Thanks!

Jeff

[Jeff Abraham]

Thanks for clearing up that "pp" confusion. Once I changed it to the carriage return then everything worked great. I will look at your previous post when I get a chance.