13 Replies Latest reply on Mar 29, 2016 10:47 AM by krheinlander

    Is there a way to see all layouts where a field is used?

    AEH

      I have unexpected results when searching in a field, and I suspect it is because the field was mistakenly placed in a portal in another layout. (I may have done this myself some years ago.) Is there a way to search the database to find any layout where that field appears?

       

      The next question is, how do I delete extraneous data from the field?  The field should not be in a portal - data is stand-alone, not related to other history, and should not have multiple records. If I delete the field from any portals, will it automatically delete extra data which used to be in other portal rows, or how do I clear it up?

        • 1. Re: Is there a way to see all layouts where a field is used?
          jbrown

          I have unexpected results when searching in a field, and I suspect it is because the field was mistakenly placed in a portal in another layout. (I may have done this myself some years ago.) Is there a way to search the database to find any layout where that field appears?

          A colleague, mislav put together a blog post about doing this. He's got a demo file you can pull the script from and use in your own solution.

           

          You can, as the post says, use a DDR (database design report)  if you have FIleMaker Pro Advanced

           

          EDIT: Here's the link to the post: What Layouts Is This Field On? - Soliant Consulting  (Thanks Beverly!)

          • 2. Re: Is there a way to see all layouts where a field is used?
            beverly

            Jeremy, do you have a link to the blog post?

             

            thanks!

            beverly

            • 3. Re: Is there a way to see all layouts where a field is used?
              AEH

              Thanks, sounds like exactly what I need, and I do have FMPro Advanced -- but unfortunately it's v. 11, and the demo is on 14.  Time for an upgrade I guess!

               

              RE: part 2 of my Q -- do you know if I succeed in finding all layouts the field is on and removing it from portals, how do I then get rid of the extra records created when it was in a portal?

              • 4. Re: Is there a way to see all layouts where a field is used?
                emory_brown

                You need the DDR, which is available in FM Pro Advanced, under Tools > Database Design Report... This will output an HTML or XML copy of the database schema. Save as HTML and then open it up in a browser and you can quickly drill down and see where a field is used.

                 

                I would recommend a tool such as Base Elements (BaseElements | Goya Pty Ltd) which is something of the industry standard for this sort of work. It's an amazing tool that will answer any similar question of "where is this X used in the system" even individual variables or custom functions.

                 

                As to your second question about cleaning up the data... the only quick way to do this is to find a difference in a record created in a portal versus the normal record data. If, for example, portal records have a foreign key filled in that the regular records do not, you can do a find and delete on that. Otherwise you'll need to manually go through the records and fix any data.

                 

                Hope that helps!

                • 5. Re: Is there a way to see all layouts where a field is used?
                  AEH

                  Thanks, it appears the DDR did just what I needed, confirming the 3 layouts my field was on.

                   

                  Now that I know this field is no longer in a portal, though, I can't figure out how to remove the records which must have been created when it was in a portal.  The way I know there are "ghost" records is that when I search in the field for empty records (find=), it returns records which are NOT empty.  Similarly, when I search for one response ("maybe") I get records which have another response ("yes"), so clearly multiple responses were entered at some point in a portal.  The latter situation I can correct by deleting all the inaccurate data -- but I don't know how to delete "empty" records which I can't even see.

                   

                  The only solution I can think of is to create a new field and reenter the correct data (not in a portal).  With only 150 records, this wouldn't take too long, but is there a simpler way I am not thinking of?

                  • 6. Re: Is there a way to see all layouts where a field is used?
                    beverly

                    If you are viewing the field as formatted (checkbox set, radio button set, drop-down list, pop-up menu) based on a value list, then yes you may have other data in the field.

                     

                    Place the SAME field (not a new one) on the layout and leave it as an Edit box field only (no formatting under Inspector->Data->Control Style). You will see the data that should not be there as well.

                     

                    beverly

                    • 7. Re: Is there a way to see all layouts where a field is used?
                      coherentkris

                      There is no way that having a field on two different layouts could contribute to find failure.

                      FileMaker searches data not interface controls.

                      You can script a find in a field thats not even exposed on the current layout as long as the relationship from data to data is established on the graph.

                      Removing an interface object will not affect the data at all.

                      To clean up data you should probably do it on a developer only layout where you completely isolate the records you want to delete and delete them. Just make sure you don't have delete related records turned on unknowingly or youll have to recover from a backup to get your data back.

                      • 8. Re: Is there a way to see all layouts where a field is used?
                        AEH

                        Beverly -- As you suggested, I do have two copies of the same field on one layout.  One is a radio button set, based on a value list with two options.  The other is the one I used in order to search for empty records, and is just an edit box with no formatting.  When I search on this blank edit box, though, I come up with strange data which must have been inserted when it was in a portal at some point.  For example, when I search on one of the values, it returns some records with the other value. That's what made me think of a portal as the problem.

                         

                        I am now realizing, on more reflection and exploration, that the source of the problem may be that the field in question is in the wrong table.  All of the other fields in this table belong in a portal, while this one field is static/permanent data, not changing with the rest of the table data. So I'm guessing I need to recreate the field in a different table and reenter the data.

                        • 9. Re: Is there a way to see all layouts where a field is used?
                          siplus

                          Every layout has a number, going from 1 to the total number of layouts. You can access it with Get(LayoutNumber).

                          If you delete a layout, the layouts are renumbered. Which means that you can loop through all your layouts via a $var going from 1 to infinite and exit the loop when go to layout (layout number by calculation) gives an error.


                          Secondly, there are instructions that will not work if a field is not on the layout, one of which is Insert Text. So if you try to Insert Text into a specific field and the field is not on the layout, you'll get an error. If you don't, it means the field IS on the layout. Do you see where I'm headed ?


                          Here is the script, at the end of running it you'll have the layout names of the layouts containing your field in $$LayoutNames.


                           

                          Set Variable [$$LayoutNames; Value:""]

                          Set Variable [$i; Value:1]

                          Set Variable [$OldValue; Value:YourTable::YourField]

                          Go to Layout [1]

                          Loop

                          Insert Text [Select; YourTable::YourField]

                          Set Variable [$Error; Value:Get(LastError)]

                          If [$Error = 0]

                            Set Variable [$$LayoutNames; Value:$$LayoutNames & Get(LayoutName) & ¶]

                          End If

                          Go to Layout [Let( $i = $i + 1; $i)]

                          Exit Loop If [Get(LastError)]

                          End Loop

                          Set Field [YourTable::YourField; $OldValue]

                          Commit Records/Requests

                          • 10. Re: Is there a way to see all layouts where a field is used?
                            coherentkris

                            Guessing is a bad idea especially when you are "moving" fields from one table to another.

                            Backups better be your friend

                            • 11. Re: Is there a way to see all layouts where a field is used?
                              user19752

                              If you put a "related filed" out side of portal, you see data from "first related record" but finding the field is done on "all related records".

                              • 12. Re: Is there a way to see all layouts where a field is used?
                                Vaughan

                                No need to Insert text, use the FieldNames function to get all the fields on the current layout.

                                 

                                This could even be done without walking through all the layouts. Instead you could use the LayoutNames function to get all the layouts in the file, then loop through these with the FieldNames function.

                                • 13. Re: Is there a way to see all layouts where a field is used?
                                  krheinlander

                                  FMXRaySpecs - a free OS X download - will allow you to take a clipboard set, and show all the field characteristics in a table view. Clicking on any field, will show the location of the field on the layout in a miniature window.