User Defined columns

[Gilles Plante]

Hi,

in the bottom pane where on gets the result of the query, there are two columns named User Defined 1, and User Defined 2. What are these columns used for ?

My guess would be that we can defined the contents of these columns, so I searched but didn't find anything about that.

Thanks

[Dave Ramsey]

Gilles,

You are correct, we have not talked about those much.  Those 2 columns are patterned after the 4 columns immediately to the left, which flag if a named function is referenced inside the current item/row.  I pre-defined 4 of them, but left 2 that you can define yourself.

To use this feature, go to the preferences, and then the "Flagged Functions" tab.  The right column has the complete name of the function you'd like to search for.  The left column has the name of the FMPerception column that should show a dot when that function is referenced.  A couple of notes:
1 - You can see that currently, User Defined 1 shows a dot when the position function is used.  This is just an example.  Feel free to override it.

2 - You can see that the ExecuteSQL() column gets a dot if the ExecuteSQL function is used, or if the @.sqlExecute function is used (from the SQL Sugar collection of custom functions).  This is just an example showing that a single column can flag on multiple functions simultaneously.  You can use this feature yourself, by adding the names of custom functions that you use that wrap standard FileMaker functions (like SQL Sugar wraps the ExecuteSQL function).  This also means that you can override any of the function columns, not just the User Defined ones.  If you want to make the Evaluate() column show a dot when the LeftValues function is used (for whatever reason), you have that option.

3 - There are no rows currently for User Defined 2.  That's fine.  You don't have to put a value for every column.  You just have to use a value for every column that you want to see dots in.

4 - This feature searches only on functions (not variables, field names, etc), and only on the complete function names.  If you're searching for a function called FormatZip, your function FormatZipPlus4 will not flag (unless you also add a line for FormatZipPlus4).  The criteria are not case sensitive.

5 - Don't include the parenthesis in your function names on the right hand side.

6 - The intended use case for the User Defined columns was for a migration of some kind.  Let's say that your system makes use of the trim() function, but that's causing problems because it's not handling trailing returns.  So, you make a new custom function for SuperTrim().  Then, I'd make User Defined 1 look for 'Trim', and make User Defined 2 look for 'SuperTrim'.  Load the DDR, and make all the dots in User Defined 1 move to User Defined 2.

7 - When you change the flagged columns, FMPerception will not update the results for currently loaded DDRs (due to FMPerceptions performance caching).  You'll have to do a File -> Refresh or otherwise reopen the DDR.

8 - If you really mess things up in the Flagged Columns, there's a button to Restore Function Defaults, so you can get back to where you started.

After typing all this up, I realized that we do have some documentation on this.  Take a look at this page, which also has a video from Todd...

https://docs.geistinteractive.com/article/60-flagged-functions-columns

 

Does that clarify things?  Do you have further questions?

[Gilles Plante]

Dave,

Yes, this answers my question.

This was a preliminary question to another one. I will expose that question here, if you prefer that I create another thread I will do.

I wonder if one can create a custom query in FMPerceptio ? Let me explain. I am going through systems that were created a long time ago and that needs to be reengineered in all the ways you can imagine. I have a case where a table has many repeating fields. I know I can export the list of fields for that table and then import back into FileMaker to do some analysis, but what I am missing is that I would like to know on which Layouts each of these field are displayed. I have done that by adding a field to a table I use to gather the interesting information, and then set the contents of those fields manually by looking at FMPerception and pasting into that field. Thing is that was quite a chore since I have 83 fields with repetitions.

That would have been nice if I could get that information right inside FMPerception and the export to CSV and import in FileMaker.

Thanks

[Dave Ramsey]

Aside from the freeform text search, there really isn't a query maker (yet).  That said, You can get there with two CSV exports bumped into FileMaker.

1 - Export Fields (flat), which will have which fields have repetitions, and what table they're in

2 - Export Layout objects (flat) (third query from the bottom), which has all the layout objects on every layout.

3 - Import both to FileMaker, and relate the two tables on field name and table name.  This assumes that table names are unique... which may not be true in a multi-file system.  In that case, add a predicate based upon the File Name field in both lists.

Then, make whatever report makes the most sense to you.  I don't know if you want to put the count of repetitions on the layout object list, or if you would prefer to make a portal of layout appearances on the field list... That'll depend upon your process, and what you're planning to do.

Will that meet your needs?

[Gilles Plante]

Dave,

your suggestion makes a lot of sense ! I will do it that way next time.

Thanks for the great support.