4 Replies Latest reply on Apr 24, 2012 12:25 PM by philmodjunk

    Making records relate to each other

      Title

      Making records relate to each other

      Post

      FMP11: Each record has unique serial #...

      I have a hidden "related records" field - which allows 2 or more records to link to each other...

      When I create a related record from the current record, the current record puts it's SR# into the new "related records" field. (this works fine).

      I can't get the new record to put its new SR# into the originating record.

      In other words, both records should end up with the other's SR# in a field.

      I'm trying to calc the next SR# before it is created & pasting it into the "related records" field before actually creating the new record... (not having luck)...

      1. How can I check the current "last SR#" and add 1 to it and paste it into a field... OR

      2. Would it be easier to create the new record first and copy its SR# - then paste it back into the originating record - then back to the new record...

      I'm looking for the cleanest solution possible.

      Thanx all.

        • 1. Re: Making records relate to each other
          philmodjunk

          Try it this way:

          Set Variable [$ID1 ; YourTable::YourSerialField ]
          New Record/Request
          Set field [YourTable::RelatedRecField ; $ID1]
          Set Variable [$ID2 ; YourTable::YourSerialField ]
          Go To Related Record [From table: YourSelfJoinHere ; Using layout: <current layout> ]
          Set Field [YourTable::RelatedRecField ; $ID2]

          You don't want to use copy/paste for this kind of thing as it destroys any data your user may have copied to the clipboard.


          • 2. Re: Making records relate to each other

            Thanx...

            But this script (without actually trying it) seems to go back and forth between records and leaves you back at the originating record...

            Set Variable [$ID1 ; YourTable::YourSerialField ] =============================> This copies current SR# (SR1) into a variable...
            New Record/Request =================================================> Create a new record...
            Set field [YourTable::RelatedRecField ; $ID1] =============================== > Paste SR1 into "new record's" field...
            Set Variable [$ID2 ; YourTable::YourSerialField ] =============================> Copy new record SR# (SR2) into a variable...
            Go To Related Record [From table: YourSelfJoinHere ; Using layout: <current layout> ] ==> Goto original record/layout
            Set Field [YourTable::RelatedRecField ; $ID2] ===============================> Paste SR2 into "original record" field

            If I plug this into my file, the screen will go back & forth and won't look very pro...

            There must be a way to get the current largest SR# of a file.

            Thanx.

            • 3. Re: Making records relate to each other
              Sorbsbuster

              "But this script (without actually trying it) seems to go back and forth between records and leaves you back at the originating record." - I thought that's what you wanted.

              "There must be a way to get the current largest SR# of a file." - there is, but that's a different question.

              • 4. Re: Making records relate to each other
                philmodjunk

                If you want to remain on the newly record, just add these steps to the end of the script:

                Unsort Records
                Go to Record/Request/Page [last]

                In unsorted order, the most recently created record will be the last record in the found set. You can also sort by your serial number field to make the most recently created record the first or last record in your found set. (The most recently created record will have the largest serial number value.)

                Apart from this script that created a new record, if you want to find the record with the largest serial number, do a show all records, then sort or unsort to make the record with the largest serial number the first or last record.

                A summary field can also report the largest serial number value in your found set.