|
|
- Existing field is a multi-value check box. Let's call this field cities
- Need to split into multiple fields without re-saving every document in the database. Let's call these fields cities_MN and cities_WI
- Step 1: Hide cities field
- Step 2: New fields cities_MN & cities_WI are editable checkbox fields with default value of cities
- This is the concept. Even before a document is re-saved this change will allow the appropriate value(s) from cities field to display in cities_MN & cities_WI fields
- How do they separate? Based on the field choices of the new fields cities_MN & cities_WI
- Pitfalls. Prior to re-saving, data in existing docs is saved in cities field but cities_MN & cities_WI fields are still null "". Need to be careful with any view selection criteria, agents, or any other processing we do
- Example:
- cities field has field choices of "Appleton, WI"; "Madison, WI"; "Woodbury, MN"; "Saint Paul, MN"; "New York, NY" and let's just say all 5 are checked, so cities field has all 5 values stored
- Pre-save & after the split
- cities_MN field has field choices "Woodbury, MN"; "Saint Paul, MN"; "Minneapolis, MN"
- cities_MN field has value stored as "", but when displayed will display "Woodbury, MN" & "Saint Paul, MN" (because the default value on this field is "cities" and Notes can display this even before it's saved and updated)
- "Minneapolis, MN" will not be displayed since it wasn't checked in the cities field
- Post-save
- cities_MN field will have value stored as "Woodbury, MN"; "Saint Paul, MN"
- dido for cities_WI field
Views and how to handle: - cities will always be in available in views, but won't be correct once a doc is saved and cities_MN & cities_WI are created, then we MUST use the new cities_MN & cities_WI fields to ensure data accuracy
- again, our cities field has field choices of "Appleton, WI"; "Madison, WI"; "Woodbury, MN"; "Saint Paul, MN"; "New York, NY", and assuming we don't want do display cities outside WI/MN, our view selection formula could look something like this:
- include := "";
- @If(@IsAvailable(cities_MN);
- values := cities_MN:cities_WI;
- values := cities;
- @If(@Contains(values; "Appleton, WI"); (include := "yes"); "");
@If(@Contains(values; "Madison, WI"); (include := "yes"); ""); @If(@Contains(values; "Saint Paul, MN"); (include := "yes"); ""); @If(@Contains(values; "Woodbury, MN"); (include := "yes"); ""); - SELECT
(include = "yes")
- our view formula could look something like this:
- @IsAvailable checks to see if the field exists, and we could have picked either cities_MN or cities_WI since they're both created at the exact same time
- this view is going to display all the cities that are checked on the form
- when the new fields aren't there we go ahead and display the old cities field
- when the new fields exist (though they could still be null "") we display cities_MN and cities_WI
- pitfall: cities is hidden and contains "bad data" once the new fields are created, and this is a drawback of splitting the field and is something we need to handle in views and other situations where we handle data
Conclusion: - It would have been better to find a way not to split the field like this in the first place, but it was already done before I got involved. Next time, I'd probably start the same way. Hide the old field and split into 2 new fields defaulted to the old field. Then I'd write an agent to re-compute & re-save all docs to force the new fields to pickup and save their defaults. Then I'd get rid of the original field. What seems like a good idea sometimes turns into a mess.
|