1 2 Previous Next 24 Replies Latest reply on Apr 3, 2016 2:45 AM by electon

    Odd behavior with FieldNames function

    Mike_Mitchell

      Good day, all. I have a system that needs to save a list of fields into a field. For purposes of abstraction, I'm using the FieldNames function to assemble the list of fields. The calculation looks like this:

       

      Let ( [

      createTSFieldName  = GetFieldName ( createTS ) ;

      tableName = Left ( createTSFieldName ; Position ( createTSFieldName ; "::" ; 1 ; 1 ) - 1 ) ;

      fields = FieldNames ( "" ; tableName & "_syncFields" )

      ] ;

       

      Case ( not IsEmpty ( modifyTS ) ; fields )

       

      )

       

      It's used as an auto-enter calculation so it can be indexed later. Now, this works a treat when I create or modify a record from that table. It also works when I create or modify a record from one relationship away via a portal or directly through the relationship.

       

      However, for some odd reason I can't fathom, this is completely failing when I attempt to modify a field from two relationships away. The relational structure looks like this:

       

           globalsTable --< virtualTable --- targetTable

       

      The relationship between the globals table and the virtual table is based on a constant calculation field (same on every record). It's indexed on both sides. The relationship between the virtual table and the target table is an unstored calculation of the Virtual List type - GetValue ( $$someVariable ; row ). This is a key field to the target table.

       

      I have a portal in globalsTable that points to virtualTable. When I use something like:

       

           Set Field [ targetTable ; someField ]

       

      from the context of globalsTable, the field comes up null. When I do the same thing from the context of virtualTable, it works.

       

      Other notes:

       

      Yes, the layout "tableName_syncFields" exists. Again, it works from targetTable and from virtualTable, so I know it's seeing the list of fields.

      The field shows some odd behavior between the time the field is set and the time you commit the record from the context of globalsTable. The calculation actually changes on commit.

       

      I'm obviously missing something here. Why would this function fail from two relational hops away, but function fine from the local context or one hop away? And, more importantly, what can I do about it?

       

      TIA

       

      Mike

        • 1. Re: Odd behavior with FieldNames function
          ninja

          Dunno if it is the same thing, but I find that updating a value two hops away will not update an unstored calc on my current table either.  Been that way for years.  A refresh is needed to make it reclaculate.

          One hop away does not need the refresh.

           

          I suspect that the "radius of watched things" goes only one relation distant.

           

          I'll let someone smarter than I in things Filemaker answer your real question...

          • 2. Re: Odd behavior with FieldNames function
            Mike_Mitchell

            Yes, I’m aware of that behavior. This isn’t an unstored calculation, however. It’s a calculation that needs to be stored on record commit, which I don’t think is the same thing (although I might be wrong).

             

            The weird part is, this has worked on several occasions. But sometimes it doesn’t - and darned if I know why.

            • 3. Re: Odd behavior with FieldNames function
              dtcgnet

              Which table does "createTS" originate in? Is it globalsTable::createTS? Is it virtualTable::createTS?

               

              This is an auto-enter when? Is the auto-enter allowed to be modified? Which table and which field is the result of this calculation placed in?

              • 4. Re: Odd behavior with FieldNames function
                Mike_Mitchell

                All fields are in targetTable. The calculation triggers on modifyTS, which is a modification timestamp field. Yes, the auto-enter can be modified. The results of the calculation go in a field called fieldsToSync, which is also in targetTable.

                • 6. Re: Odd behavior with FieldNames function
                  Mike_Mitchell

                  Okay, update. I tried this calculation in the Data Viewer:

                   

                       Let ( [

                            createTSFieldName  = GetFieldName ( Contact::createTS ) ;

                            tableName = Left ( createTSFieldName ; Position ( createTSFieldName ; "::" ; 1 ; 1 ) - 1 )

                       ] ;

                   

                            Substitute ( FieldNames ( "" ; tableName & "_syncFields" ) ; Char ( 13 ) ; "<cr>" & Char ( 13 ))

                   

                       )

                   

                  This returns a good list of fields, like this:

                   

                       Name_First<cr>

                       Name_Middle<cr>

                       Name_Last<cr>

                       Gender<cr>

                       DOB<cr>

                       SSN<cr>

                       PrimaryLanguage<cr>

                       Address_Street<cr>

                       Address_City<cr>

                       Address_State<cr>

                       Address_Zip<cr>

                       Email<cr>

                       Phone<cr>

                       Company<cr>

                       Notes

                   

                  However, the exact same calculation in a field (whether it's an auto-enter calc or a full-blown calculation field), when the record is created from two hops away, gives me this:

                   

                       <cr>

                       <cr>

                   

                  So it's not returning nothing, as I originally thought; it's returning a couple of carriage return characters. Which is really strange, considering the actual list of fields is way longer than two values.

                   

                  Anyone have any ideas on this? It's pretty important; I have a couple of production systems where this could really cause some problems.

                  • 7. Re: Odd behavior with FieldNames function
                    beverly

                    How about using ExecuteSQL() with the Meta data?

                    beverly

                    • 8. Re: Odd behavior with FieldNames function
                      Mike_Mitchell

                      It’s not a bad idea, but the problem is, I don’t want all the fields in the table. I just want selected fields, which I why they’re on a dedicated layout. I’d have to store the list of fields some other way, which is going to be either (a) in data, or (b) hard-coded.

                       

                      The other problem with that is I’d have to store it in the solution, because this is for syncing. I can’t just assemble it at runtime. I have a separate field that logs the changes in the fields. That’s done continuously as people edit data, but it’s contingent on this list of fields to function properly. So having it local is crucial. I have to be able to track record changes on the host and on the local version when they’re not connected.

                       

                      When I first designed this, I went round and round trying to figure out a way to store the list of fields to log. It needs to be on every record (unfortunately) for the logging field, and rather than require another table on both hosted side and local side (that every table would have to tie to - ew), I went with the design function. It seems to work most of the time, but every once in a while, I get this behavior.

                       

                      So the requirement is:

                       

                      - list of fields to process

                      - stored locally

                      - accessible to every table

                      - available on every record

                       

                      Any other good ideas?  

                      • 9. Re: Odd behavior with FieldNames function
                        dtcgnet

                        You say, "when the record is created from two hops away". If you refresh the calc after the record exists, is the list of fields still empty? Is the relationship involving targetTable sorted ever? Any chance you can post a stripped down sample file?

                         

                        Sent from my iPhone

                        • 10. Re: Odd behavior with FieldNames function
                          Mike_Mitchell

                          It works fine once I commit the record if I do something that causes an update (like modify the record) from the file & table where the calculation lives.

                           

                          The relationship is not sorted.

                           

                          I’ll see if I can come up with something that demonstrates the issue. It’s pretty complicated, though, in its current form.

                          • 11. Re: Odd behavior with FieldNames function
                            dtcgnet

                            Just a guess...

                             

                            When you are going from virtualTable to targetTable, your relationship is based on an unstored calc. That's okay because the unstored calc is not on the "far" side of the relationship.

                             

                             

                            But when you are going from GLOBAL to Target, "The relationship between the virtual table and the target table is an unstored calculation of the Virtual List type - GetValue ( $$someVariable ; row ). This is a key field to the target table.", then an unstored calculation IS used in a relationship on the far side of the relationship.

                             

                            For a>b>c, a's relation to b is straightforward. But a's relation to c includes an unstored calc in the middle.

                             

                             

                             

                            Sent from my iPhone

                            • 12. Re: Odd behavior with FieldNames function
                              Mike_Mitchell

                              It’s a good thought, but no. I tested it by replacing the unstored calc with a fixed text field and updated it with a script. Same result.

                              • 13. Re: Odd behavior with FieldNames function
                                dtcgnet

                                Can you describe the process a bit more? User makes a change in table, time stamp updates, auto-enters update as a result. Any scripting in there? Any triggers? Any layout switches?

                                • 14. Re: Odd behavior with FieldNames function
                                  Mike_Mitchell

                                  None of the above. No triggers, layout changes, or scripting. In fact, the records are being held open via a portal for transactional updates (so we can revert if something goes kerflooey).

                                   

                                  The process you describe is exactly what happens when users are editing data. And it works fine under those circumstances. It just blows up when attempting to update from the globals table.

                                  1 2 Previous Next