4 Replies Latest reply on Jun 14, 2010 3:54 PM by philmodjunk

    Setfield only working on first record on a different table

    smkoonjul

      Title

      Setfield only working on first record on a different table

      Post

      Hi,

       

      Been going through the forums the last couple months trying to build a lending database (FM 10). It's been really helpful.

      I'm having a problem with setting a field from another table and it's probably a good time for me to ask for help.

       

      I have a Reports Table where when the file is opened, a new record is created listing the consultant logged in, date time etc and then has a field for all Check Outs by users during this consultant's shifts.

       

      Everytime a user checks out an item I run a check out script which creates a new record in Transactions with the item, user, date time etc. During the script, I want to append to the Check Out field of the record in Reports with a specific string of info.

       

      SetFeld[Reports::Check Out; Reports::Check Out & Users::UserID & " - " & Equipment::Serial Number & "¶"]

       

      So everytime a check out occurs, that field in Reports is added to.

       

      The problem I'm having right now is that even all the Check out lines are going to just the FIRST record of the Reports table and not the most recent one. So even when I close the file, reopen, new record created in Reports, the Check Out script still inserts the text in the field of the first record in Reports.

       

      If anyone could help, that'd be great.

       

      Thanks

      SK

        • 1. Re: Setfield only working on first record on a different table
          fitch

          You could either: go to the correct record in reports; or use a relationship sorted descending; or use a reports portal and go to the last row.

          • 2. Re: Setfield only working on first record on a different table
            philmodjunk

            It would help to see more of your script. I believe your script is switching between two or more layouts and that's where the problem lies.

             

            A step like Set FIeld refers to fields and tables from the context of the current layout. (Each layout specifies a specific table occurrence box in Manage | Database | Relationships as specified in "Show records from" in Layout Setup...) When you switch layouts, this change in table context means that the records referred to will be determined by this table occurrence and how it relates to other tables in the relationship graph. It has nothing to do with what record is "current" in the other layout.

             

            Here's the work around:

            Use Set Variable to capture the relevant data from the current record in your first layout and then, after switching layouts, use this variable as the expression in the second parameter of your Set Field step to finish appending the data to your field:

             

            Set Variable [$UserIDSerial ; Value: Users::UserID & " - " & Equipment::Serial Number & "¶"]

            Go To Layout [Reports]

            SetFeld[Reports::Check Out; Reports::Check Out & $UserIDSerial ]

             

            On a final thought, instead of appending a series of lines of text into one text field, it is likely a better approach to simply create a series of related records for this. A set of related records can be much easier to work with in these kinds of situations.

            • 3. Re: Setfield only working on first record on a different table
              smkoonjul

              Thanks for the responses.

               

              Yes, I do go to another layout in the checkout script. The first layout is for consultants to select the equipment and the user checking the equipment out, and then it moves to a transactions layout.

               

              Global variables worked, just moved layouts and set the field and then moved back.

               

              The reason I'm appending to the same field is that consultants submit an online Shift-End-Report (with all their other tasks including lending) and this is an easy way to allow them to just copy the contents of the field and just paste it in the online form.

               

              Thanks again,

              SK

              • 4. Re: Setfield only working on first record on a different table
                philmodjunk

                You could have both by using the list function to pull all the related entries into a single text field for copying and pasting. That list will have each entry separated by returns just like your scripted append approach does.