Showing only value lists related to a specific field

[bluearrow]

Hi,

I am new to FM7-8.5 (have been working on FM6 for years, but I am updating my knowledge now).

I have been struggling with value lists. I would like to create a table that holds all my value lists, for my whole solution (one file). In any specific table, I would like to be able to have any field formatted as drop down (or whatever) to show only the list of values that are pertinent for that field.

For instance:

Table1

ID

Field1 - Colors (drop-down) must show only Colors coming from Table2.

Table2

ID

Data (Colors, Models, Makers, etc.)

Data_Code (all colors would have the same code, all models will have the same code, etc.)

I understand this could easily be done with "standard" value list, but for administrative reasons (to long to explain here) I need to delegate value list creation and editing to somebody else. Creating value list through another table is much more flexible and easy to manage (centralized management).

I would not like to create a field in every record in Table1 to establish the relationship:

Table1::Data_Code>Table2::Data_Code

Although this would allow me to create the relationship I am looking for, it will also force me to create as many numeric fields as neeeded for every value list I neeed to show, in every table, for every filed that needs a value list. Not a good solution to me.

Any ideas? A sample file would greatly be appreciated.

Thanks

[Ender]

Hey bluearrow,

No need to create multiple fields for this. You were on the right track with a multipurpose table. What you need in the Value table is two data fields, and possibly an ID. The first data field is the Value Type, the second is the Value. Then you just need to use a filter in whichever table to relate to this Value table via the Value Type, and a value list defined to use those values (showing related values only). For filters in the target tables, it's generally most efficient to use globally stored text fields (they just need to be populated with the right Value Type prior to use).

This is essentially a 'conditional value list'.

[bluearrow]

Thanks.

I am trying to figure out how to do it. I will get back to the forum. I am having problems with the field names: if the user selects a field, I have to pass this parameter to the script or to a global field, in order to indicate which value list should be visible. This implies that field names must be static and that some values in the script (the parameter) must also be static. I am not sure I like this.

Regards

[Ender]

I'm not sure what you're stuck on. Field names generally are static. But field names don't really have anything to do with value lists.

[Lee Smith]

Except of course for basing a value list on one. ;)

[Ender]

Nice try, but a value list based on a field still doesn't care what the field's name is. The field name may change, but the value list still works.

[Lee Smith]

No, that wasn't my point. I meant that you would need to know the field name initially for picking it for the Value List.

Lee

p.s.

This was just a joke, if it is offense to you, let me know and I'll remove it.

Edited November 29, 2006 by Guest
p.s.

[bluearrow]

I have prepared a file that shows what I have gotten and what I need. It mostly works fine, but there are some flaws and I would really appreciate your opinions on how to improve it. It is based on a system I had in FMP6, that it is not really my idea (I think I started from an idea by Matt Petrowsky).

1.- You will see that an administrator can control all value list of a solution from a single layout (Admin). This is very useful.

2.- In the Data input layout I have included different input systems:

- button based on script parameter (I need to input the field name)

- button and Go to field script

- clicking on the input field and passing the script parameter.

Each method has its advantages and disadvantages, but the one I would like to use is the one it is not working (using a popup menu). This is the most user friendly method for a value list and saves layout space and time (mimics the original value list behavior).

Any ideas on what it is wrong?

How can it be improved? (for instance, if the developer changes a field name, several scripts would need updating, and this is not a good idea if you are developing a big solution).

Regards.

VL_testv01.zip

[Ender]

No worries, Lee. We all know you're a joker.

Actually, it turns out in bluearrow's case, the field name is tied to the value list. Go figure.

[Ender]

blue,

I think I see what you're up to, but I don't really like it. It seems much easier to just tie different value lists to the right fields in the first place, rather than trying to make a single generic value list and have to deal with all the mumbo-jumbo in the background to make it work.

[bluearrow]

blue,

It seems much easier to just tie different value lists to the right fields in the first place...

Could you give me an example on this (sample file) or guide me to a sample file if it has already been posted in here or somewhere else. I do not know what I should look for, and I am not sure what you mean in your comment. Do you mean just using the standar value lists included in FileMaker or something else?

Thank you.

[Ender]

Hey blue,

Try this on for size.

VL_testv02.fp7.zip

[bluearrow]

Thank you Ender, for the time taken to create the sample file. I have checked it, and it really is much simpler than the one I uploaded. However, the kind of functionality I need is not present. A delegated administrator can easily add and delete values to an existing value list but, taking into account that the delegated administrator has no access rights to FileMaker own's value list dialog, neither to the database dialog for creating fields:

- how can he/she create a new value list?

- how can he/she delete a value list?

The main reason behind all this development is to create a functional system that could allow a delegated administrator the creation, adding and deletion of value list and its values, without touching any of the standard FileMaker dialogs. I use SecureFM to ensure that no standard dialogs are accessible. It seems that the only way to go is to improve in something similar to what I uploaded to he forums: a table that includes both the value list name (well, sort of), and the values in a single or a couple of tables. Then the user would be able to modify, add or delete field values or records, what in effect turns out to be a modification on the value lists itself. I am still working on this...

[Ender]

If they don't have access to define fields, why would they need to add a value list to a field or remove a value list from a field? The developer should be the one to assign value lists to fields.

[bluearrow]

If they don't have access to define fields, why would they need to add a value list to a field or remove a value list from a field? The developer should be the one to assign value lists to fields.

Well, it is not really my decision. :

The general manager of the company that hires me as a developer is used to having some user rights in the database. Among this, he likes to add values to a value list and "create new value lists".

I do not really like this, and the only way to somehow control this kind of actions is by using a different system altogether. You see, he does not know FileMaker at all, neither any other databases. Besides, I personally find it easier to have a centrally located table for value lists: value list names and values.

Additionally, this database gets audited from time to time, and to my knowledge it seems simpler to show the "personal value list system" instead of FileMaker value list dialogs. I might be completely wrong, and it might be that is the lack of knowledge that is driving my decision regarding this two issues.

Regards