Consolidating lists

[LaRetta]

Hi everyone,

I have a parent (Site), child (Inspections) and grand-child (Parts) relationship.  I have a field in Site and a field in Parts which have both inherited the same checkbox value list (file with sample data attached)

What I need:

1)  consolidate only a single set in all items into Site::fieldList1 for the parent.
2)  many of the values in both Site and Parts have spaces before or (possibly) after the value
3)  If there is more than a single value in the result in Site::fieldList1 and one of the values is 'none', I need the 'none' dropped

I considered recursive custom function, trimming the spaces first and then using ValueListItems() or using script to loop.  As I ponder my options, I would sure appreciate any high-level ideas of the best approach.  Thank you!

CompareLists.fp7

BTW, this is for a one-time cleanup and data migration of multiple individual files ( cities ).

I also do not want to add calculations to the file so I suppose that limits me to script although a custom function to assist script would be fine.

Edited February 2, 2016 by LaRetta
added word 'checkbox'

[LaRetta]

What I would probably do is start at the Parts table and loop them, trimming first, testing if the value is in Site and then setting the Site.  But of course I'll have to trim the Site record as well.  It all feels a bit twisted.

[comment]

43 minutes ago, LaRetta said:

I also do not want to add calculations to the file

Would you add a value list?

 

Edited February 2, 2016 by comment

[LaRetta]

The new file will have a valueListItem table related to a valueList table eventually but it currently has the bogus value list with beginning spaces and I plan to clean that up so yes, it does.  :-)

I also believe I'm wrong about possible trailing spaces.  As for leading spaces, some of the files were corrected with the leading spaces and some not.  The value list with spaces is included in this sample file and it is in the master I am working on.

Edited February 2, 2016 by LaRetta

[comment]

I am afraid that went over my head...

Anyway, I suggest you start by finding the Site records that have anything in the fieldList1 field OR in the Parts::fieldList2 field.

Then populate the target field (in Sites) by replacing its contents with:

List ( fieldList1 ; List ( parts::fieldList2 ) )

Now you have to trim the values individually, and de-dupe the list in each record. For de-duping, you could use a value list in a self-join to the same record - but I guess for a one-time cleanup a looping script might be preferable.

[LaRetta]

Ah, combine into parent first!  Understood and script it is.  Is there any reason that this can't be done, via script, within a variable instead of the field?

[comment]

5 minutes ago, LaRetta said:

Is there any reason that this can't be done, via script, within a variable instead of the field?

No, it's purely a matter of preference. I went for a field, because I thought of using a value list for the de-duping - and the field must be there anyway to hold the final result.

[LaRetta]

That was perfect, Michael.  Thank you so much!  

[LaRetta]

I'm fussing over this.  It worked great and then I realized I needed to still remove the 'None' if more than a single value and I am plagued by trailing carriage returns.  In truth, I should be able to do this all within a single Set Field[] step instead of two but no matter what I try, I get trailing return.  The first Set Field[] works (although I had to apply a kludge to remove the trailing return) and the failure is in the second.  I can apply a kludge to the second Set Field[] as well, I guess, but can you suggest a cleaner approach?

# FIND RECORDS
Go to Layout [ “site” (site) ] 
Enter Find Mode [ ]
Set Field [ site::fieldList1; "*" ] 
New Record/Request
Set Field [ parts::fieldList2; "*" ] 
Set Error Capture [ On ] 
Perform Find [ ]
If [ not Get ( FoundCount ) ]
Exit Script [ ] 
End If

# COLLECT VALUE LIST VALUES ( this may be from a table when migration takes place )
Set Variable [ $valueList; Value:ValueListItems ( "" ; "OtherPermits" ) ]

# we are on first record
Loop
Set Field [ site::fieldList1; 

Let ( [
  allValues = List ( site::fieldList1 ; List ( parts::fieldList2 ) ) ; 
  expand = Substitute ( allValues ; "¶ " ; ¶ ) ; 
  values = Substitute ( TrimAll ( expand ; 0 ; 0 ) ; "  " ; ¶ ) ; 
  result = FilterValues ( $valueList ; values )
];
Left ( result ; Length ( result ) - 1 )
)]

If [ ValueCount ( site::fieldList1 ) > 1 and Position ( site::fieldList1 ; "None" ; 1 ; 1 ) ]
  Set Field [ site::fieldList1; Substitute ( ¶ & site::fieldList1 & ¶ ; "¶None¶" ; "" ) ] 
End If
Go to Record/Request/Page [ Next; Exit after last ] 
End Loop

If not, I'll just kludge and two-step this puppy.  :-)

BTW, I was incorrect in that all cites are using a value list identical to the one in the file I presented.  ALL have beginning spaces so that simplified my need.

Edited February 2, 2016 by LaRetta

[comment]

Does the "OtherPermits" value list include all possible values you want to keep? You didn't mention this earlier. If true, then your task is much easier.

But I don't understand this part:

values = Substitute ( TrimAll ( expand ; 0 ; 0 ) ; " " ; ¶ ) ;

How can you do this, when you have legitimate values with space/s in them, e.g. "Air Quality"?
 

[LaRetta]

"Does the "OtherPermits" value list include all possible values you want to keep? "

Yes.

There are two spaces there.  It doesn't look like it translated properly.

I've corrected the code.  I had to correct it several places when I pasted here and I missed one.  :-/

The rest of it appears correct.

Edited February 2, 2016 by LaRetta

[comment]

I have another preliminary problem: why are there leading spaces in the "OtherPermits" value list?

[LaRetta]

I have removed them.  Sorry, they should be removed in your file as well.

That's how the value list originally was served up and why the results included spaces.  It is now corrected in the master file.

[comment]

Okay, so would this work for you, as the calculation for Set Field (or Replace Field Contents)?

Let ( [
valueList = ValueListItems ( "" ; "OtherPermits" ) ;
allValues = List ( site::fieldList1 ; List ( parts::fieldList2 ) ) ;
trim = TrimAll ( allValues ; 0 ; 0 ) ;
clean = Substitute ( trim ; [ " ¶" ; ¶ ] ; [ "¶ " ; ¶ ] ) ;
keep =  FilterValues ( valueList ; clean )
] ;
If (
ValueCount ( keep ) > 1 and not IsEmpty ( FilterValues ( "None" ; keep ) ) ;
Let ( remove = Substitute ( ¶ & keep ; "¶None¶" ; ¶ ) ; Middle ( remove ; 2 ; Length ( remove ) - 2 ) ) ;
Left ( keep ; Length ( keep ) - 1 )
)
)

Note: I have not tested this.

---
P.S. Since "None" will always be the first value, you could simplify this and just remove the first value if the test is true.

 

Edited February 2, 2016 by comment

[LaRetta]

A few points:

1. I hadn't considered searching for values in both tables before beginning.  That cut the task in half.
2. I hadn't considered RFC since I rarely use it, but it is perfect here since this will be stand-alone files during migration process.
3. I hadn't considered removing None from first value!
4. I originally started to write this and tested it using TrimAll() (please see screenshot).  The results are no different than Trim() so we can use Trim() instead.

As you say, this was untested but it certainly works and it amazes me that you can put it together without testing!  

Regardless, I ran yours through 400+ records and it is perfect (no surprise).  Thank you so much for assisting me in learning better approaches and ways of thinking.  I really appreciate it!

Oh wait.   You used TrimAll() in case there were other spaces between.  Right?  My bad!  I shall use TrimAll()!!

Edited February 2, 2016 by LaRetta

[comment]

I used TrimAll() in case you happen to have a value followed (or led) by two or more spaces. The Substitute() handles only one leading and one trailing space.