Convert from "Use Custom Values" to "Use Values From Field"

[feeei]

Hi everyone,

 

I'm working on a FM database that was created by the predecessor of my position. We are due for an upgrade really soon (FM9 -> 12) and I want to make sure that I clean up the database as much as I can before migrating. The idea is to merge multiple tables into one file rather than having multiple files, and to also establish relations between these tables. 

Here's a challenge I'm coming up against - my current tables make much use of the "Custom Values" field in the Edit Value List menu. I want to convert it to "Use Values From Field" while using the old value to create links to the related table. For instance, one field has the Custom Value field filled with the initials of Project Managers. Instead, I want these values to originate from a "Staff" table, selecting only staff that are assigned a staffType called "Project Managers". It would be even better if we could link to staffID instead of the initials of the person. 

 

The sticking point is that I want this change to be "automatic", so that I don't have to go through thousands of entries, reselecting the right values.

Does anybody have a solid working method for converting value lists from "Custom Values" to "Use Values From Field"?

 

Any input is much appreciated.

Fei

[Lee Smith]

Hi Fei, and welcome to the forums.

 

Am I understanding you correctly that you have relationships based on Values in a List?

 

Relationships need to be based on unique values and work behind the scene and not be modifiable.

[bruceR]

The staff, initials, StaffID part seem fairly straightforward. 

 

I don't think there is any way to automate the change. And I think it is important to understand some other aspects of the difference. Particularly - the order of the appearance of the choices.

 

One significant difference is that for field-based values, they will ALWAYS appear in strict alphabetical order. (There are some techniques for dealing with this but they add more to the workload of this change).

 

So for instance if you have a custom value list:

 

Received

-

Invoiced

Shipped

-

Paid

 

It will appear as:

 

Invoiced

Paid

Received

Shipped

[feeei]

Thank you Lee Smith! You wrote: "Am I understanding you correctly that you have relationships based on Values in a List? Relationships need to be based on unique values and work behind the scene and not be modifiable."
I do not have any relationships to anything at the moment. My use of the term relationship was a bit loose in my first post, I will try to control my language. I want to ensure that the entries used in pop-ups are drawn from a central source, especially when the entries in reality have much more information attached to them, such as a table on Staff members. 
 
BruceR, I see what you mean. The order will not matter in my case - I want the values to dynamically be created by fields that represent "current" project managers. Storing project managers as a custom list seems short-sighted to me in terms of database integrity - what if they change? Project Managers should be drawn from a source that lists the most current project managers when making a new project, while older projects that are completed should retain the original value for archiving purposes even though that person is no longer with the company.
 
I was trying to be brief in my original post, but I think it would be better to elaborate so you have a better idea of the end goal.
The larger ambition of the database renovation is to create table relationships where there previously was none. There are many cases in my database where I want Custom Values to be replaced with values that refer to records in other tables. Originally we only recorded project information in FM, with fields like team members, client, address, project description, status and stats. Then we had other disjunctive info like Staff (Team) stored in excel, client info in Outlook, etc... Needless to say, most of this information is of no use to us when we can't find it. Instead, we're looking to consolidate it all under one system - FileMaker. And when it all gets consolidated, they should draw from one another as much as possible to encourage data integrity and less manual input of duplicate information. As an example, all fields that assign staff members should be drawn from a value list that originates from the Staff table. In the end, I want to be able to do cool stuff like look up a staff member and see a portal that lists all the projects she/he has worked on by filtering the Project table with that person's staffID. 
 
======================================
Here's an example of what I have today:
======================================
 
 
table: Project
field: projectManager, custom VL (value list): "GH", "FF", "TH", "PD", "CU" (initials of project managers)
 
 
 
======================================
In the future, I'm looking to transform the setup to something like this:
======================================
 
table: Project
field: projectID
field: projectIsManager, hidden. Data: "Associate" --> relate to Staff_PM::staffType
field: projectManager, pop-up using field-generated VL: first field - Staff_PM::staffID and second field - Staff_PM::staffInitials 
field: projectIsTeam, hidden. Data: "Team" --> relate to Staff_Team::staffType
field: projectTeam, pop-up using field-generated VL: first field - Staff_Team::staffID and second field - Staff_Team::staffInitials 
 
 
 
table: Staff
field: staffID, auto-generated.
field: staffName
field: staffInitials
field: staffType, pop-up using custom VL: "Principal", "Associate", "Team", "Admin"
 
 
table: Staff_PM (duplicate table that filters Staff by "Associate" via the field projectIsManager)
table: Staff_Team (duplicate table that filters Staff by "Team" via the field projectIsTeam)
 
 
=====================================
 
 
I think I know what I would do if I started from scratch.  However I'm working with existing tables and existing values - values stored as custom values in a value list - that I want to swap using values from another table.
 
 
Going back to the original question:
 
Can I convert the "static" information stored in the custom value list to refer to a field in a different table instead? If I simply switch from "Use Custom Values" to "Use Values From Field", making sure that the fields that it pulls from are EXACTLY THE SAME as the ones in "Use Custom Values", can I trick the fields that have stored values in the existing records
into thinking that it now is pulling values from fields in another table?
In addition to that, phase two of the trick would be to change the main key from being staffInitials to being something truly unique like staffID (We're already having the problem that some new team members have the same initials as old team members.) Hypothetically speaking, once the current values ("GH", "FF", "TH", "PD", "CU") are drawn from a field-based VL, the dream scenario would be that I simply go into the "Specify Fields for Value List" menu and swap the "first field" from staffInitials to staffID. Would any one of you be able to verify if my thought process on this holds water? Is there a better way to do this? 
 
I hope I'm making sense here... please let me know if anything needs clarification and I would be more than happy to provide you with it.
 
Much much appreciated, 
Fei

[feeei]

I have no experience making scripts in FM, but I thought of another possible solution:
 
In the old table, would it be possible to create a new field that runs a CASE script, saying something like:

IF project::projectManager == GH, THEN staff::staffID == 015
IF project::projectManager == FF, THEN staff::staffID == 021

and so forth with all the entries in the old custom value list?
Then once all the values are properly mapped in the new field, I can delete the old field that contains the Custom Values list.

[Rick Whitelaw]

Feeei,

Not a good idea.

One way to determine current Project Managers is to create two fields in the Roject Managers table. Date Started and Date Finished and relate this to a parent table with a Current date field. Date started < today AND date finished > today. You will always have a current set of PMs.

Oh, and in the PARENT table, make sure your current date field, Get(Current Date) I believe, is an un stored calculation with date type result.

[bruceR]

It was not clear to me what "go through thousands of entries" means.

 

Thousands of value lists? Lotta work.

 

Or thousands of RECORDS which have fields that pull from a small number of value lists?

 

Having read your message again, it now seems like the latter condition.

[feeei]

Feeei,

Not a good idea.

One way to determine current Project Managers is to create two fields in the Roject Managers table. Date Started and Date Finished and relate this to a parent table with a Current date field. Date started < today AND date finished > today. You will always have a current set of PMs.

Oh, and in the PARENT table, make sure your current date field, Get(Current Date) I believe, is an un stored calculation with date type result.

 

Rick, what exactly from what I described is "not a good idea"? I do not have a Project Managers table (unless you mean the duplicate of the Staff table that is filtered by staffType=Partners). As described previously, in this particular case I have a Project table that contains all project-related info, and I have a Staff table that contains all Staff-related info. Staff includes people who are project managers, and they will be classified as such since all Associates are project managers. The ambition is not so much about finding who is project managing what today, but to find all projects a staff member has been part of under their staff record. Imagine FM being able to output a pseudo-CV for a staff member.

I'm trying to convert what was a list of "Use Custom Values" with people's initials, into a list drawn from "Use Values from Field" that draws fields from Staff::staffType = Associates while converting the value of thousands of records that already have this information entered. The reason for this is to mostly retain data integrity (mis-typing someone's initials = project does not get associated with that person = bad form), and plus, someone's initials should not be the key for identifying a staff members per project, a unique ID should be used instead. That's another issue I'm trying to address in this particular situation.

 

 

It was not clear to me what "go through thousands of entries" means.

 

Thousands of value lists? Lotta work.

 

Or thousands of RECORDS which have fields that pull from a small number of value lists?

 

Having read your message again, it now seems like the latter condition.

 

Bruce, yes you got it. It is thousands of records. 

[Rick Whitelaw]

It looked to me that you were manually, or by script, assigning PRimary Keys. If so, there are likely better ways to do this.

[feeei]

After writing my post #5 I set out to see if what I said could work... and as a result I managed to work out a "simple" solution for now to convert these fields.

In case someone else might find this useful, I have summarized the workflow below. In essence, what I did was this:

 

Assumption: You have an old field with useful values, but it was not set up the way you wanted it.

A. Create a new field that works exactly the way you want it to. In this case I wanted a conditional value list that drew values from another table.

B. Run a calculation on the new field, mapping the value of the old field to the desired value of the new field. This is a one-time use script meant to convert the values in old records to the "new system".

 

To elaborate on A and B...

 

A. Make a conditional value list.

 

1. I had two tables - one Source (Staff) and one Destination (Projects) table. The Source table supplies the values we want to pull from. The Destination table is the table where the values of the Source table will be used. 
2. The Source table (Staff) should us a field like staffType which contains a "Use Custom Value" List that classifies staff between different types like Partner, Associate, Staff and Admin.
3. In the Destination table (Project), create a field that will contain the value you want to filter the Source for. In this case, it is "Associate". Create a global field called Projects::isPM and enter in the Auto-enter  > Data: Associate.
4. In the Manage Database > Relationships, create a copy of your Source table (Staff) and call it Staff_isAssociate. Create an equal relationship between the field Projects::isPM with Staff_isAssociate::staffType. This will filter this particular table to only return staff that are Associates.
5. In the Destination table (Projects), create a field called Projects::projectManager which will be a pop-up of Staff members that are Project Managers/Associates. Create a new value list called list_isAssociate and "Use Values from Field" > Staff_isAssociate::staffID (first field) and Staff_isAssociate::staffInitials (second field). Select "Include only related values starting from" and pick the Destination table (Projects). 

B. Transfer the value from the old field to the new field

 

 

To select the identical Associate in the new field based on the value of the old field, the following calculation was used in Records > Replace Field Content... > Replace with calculated result on the new field (Project::projectManager) 

 

Case (
${projectManager_old} = "GH";8;
${projectManager_old} = "FF";13;
${projectManager_old} = "TH";14;
${projectManager_old} = "PD";19;
${projectManager_old} = "CU";26;
)

In Plain English: If the field "projectManager_old" equals to "somebody's initials in the old list", return a "numeric value" to the new field. Repeat and modify line for however many values you have in your old list.

The "numeric value" is the staffID I know is associated with the corresponding Associate in the staff_isAssociate table, which is the first/primary field we use in step 5 when defining the list_isAssociate.

 

Result: The calculation ends up picking the right Associate in my new field, and I'm now free to delete the old/obsolete field.

Note: Keep in mind that it makes sense for me to assume that all Associates are Project Managers - this might not be the case for everybody.

 

Could I have solved this in a different way? I welcome anybody's two cents since there are a number of scenarios where I want to convert one "dumb" field to becoming a "smart" field.