12 Replies Latest reply on Dec 14, 2010 9:48 AM by philmodjunk

    How to copy a found set to a portal?

    ferdinand

      Title

      How to copy a found set to a portal?

      Post

      Hi,

      I am fairly new to Filemaker (Pro Advanced 11), working on my first database.
      I would like to copy records from a found set to a portal, in order to create a new offer.

      I have the following tables (many-to-many):


      [Object Table]
      ObjectID_pk

      [Object-Offer_join Table]
      ObjectID_fk
      OfferID_fk

      [Offer Table]
      OfferID-pk

      I want do a search in the Object layout. The found set shall be copied to the portal in the Offer layout. Afterwards the Offer layout shall open with a new record/new ID, showing the copied found set in the portal.

      Any help is appreciated.

      Thanks in advance 

      Ferdinand

        • 1. Re: How to copy a found set to a portal?
          philmodjunk

          Can you explain what you are doing with an example? What you want to do doesn't quite make sense to me...

          • 2. Re: How to copy a found set to a portal?
            ferdinand

            We have several thousand of art works in our object table. From this table we select various artworks for to create offers to clients. Using the usual pull down menu for to select artworks in the Offer portal would be extremely inconvenient.

            Therefore my idea: we do a search in the Object table, press a button and send to the found art works to the offer layout, which will open immediately for further editing (adding the address from the contact table, e.g.).

            I hope, that this explains my problem.

            Ferdinand

            • 3. Re: How to copy a found set to a portal?
              philmodjunk

              Ok. I get it. You need to build a list in Object_Offer_Join to list all the objects that make up a given offer. Since the same object can be listed in more than one offer, you need the join table to make this work.

              The key here is to get the correct OfferID assigned to the join records.

              If you save the current OfferID in a global field, $$OfferID before starting your search, this script can add the object ID's of the current found set to the Join Table:

              #This script should be run starting from the Objects layout after a find has been performed
              Go To Record [ first ]
              Freeze Window
              Loop
                Set Variable [$objectID ; Value: Object Table::ObjectsID_pk]
                Go To Layout [Object_Offer_Join]
                New Record/Request
                Set Field [Object_Offer_Join::ObjectID_fk ; $ObjectID]
                Set Field [Object_Offer_Join::OfferID_fk ; $$OfferID]
                Go To Layout [original layout]
                Go To Record [exit after last ; next ]
              End Loop

              • 4. Re: How to copy a found set to a portal?
                ferdinand

                Thanks PhilModJunk!

                But how can I assign a new OfferID to the current found set automatically (it should be included in the script you had been so kind to provide in your last post)? 

                Like: I create found set. I start a script which will create new Offer record with new ID, and then the script copies the found set to the joint table (see your script) where it will be linked to the new Offer ID?

                • 5. Re: How to copy a found set to a portal?
                  philmodjunk

                  Add these steps immediately after Freeze Window:

                  Go To Layout [//specify offer layout]
                  New Record/Request
                  Set Variable [$OfferID ; Value: Offer Table::OfferID_pk ]

                  And change $$OfferID to $OfferID lower down in the script as this no longer needs to be a global variable.

                  At the end of the script, you can add a Go To Layout step to switch the user to the Offer layout so that they can see and complete their new offer record and see the objects listed in a portal.

                  • 6. Re: How to copy a found set to a portal?
                    ferdinand

                    I guess I don't know the correct terms for to describe my project yet.

                    So here it goes:

                    For to create a new offer I create found set in my Object layout. I click on a button which starts the script: the found set will be pushed to to the list in OfferObject join table. And then the Offer layout will open with a new offer (i.e. with new OfferID, which is linked to the found set) which contains the object found set. 

                    Content of the Offer layout: portal with the object found set, OfferID, plus relation to contact table, e.g.

                    I hope that this is more clear. 

                    • 7. Re: How to copy a found set to a portal?
                      philmodjunk

                      My script, with the additions from my last post should do exactly that.

                      • 8. Re: How to copy a found set to a portal?
                        ferdinand

                        Many, many thanks!

                        It works perfectly. 

                        • 9. Re: How to copy a found set to a portal?
                          ferdinand

                          I have just one additonal problem: When I create a new offer and submit the found set, the first row in the Offer portal stays empty...

                          What can I do to change this?

                          • 10. Re: How to copy a found set to a portal?
                            philmodjunk

                            That suggests that you have a blank object record in your original found set. You would need to find and remove that record. Put the ObjectID field in your portal temporarily and that way you can identify the record that is showing as blank in your portal.

                            • 11. Re: How to copy a found set to a portal?
                              ferdinand

                              Sorry for answering so late, but I was traveling in the recent days. 

                              My last observation about the additional portal row was not correct. Actually FM is not adding an empty row, but the first record is missing in the offer portal, instead the first row stays empty. 

                              I have no idea what it is causing this error...

                              • 12. Re: How to copy a found set to a portal?
                                philmodjunk

                                What fields do you have in this portal and from which table?

                                Clearly, the script is creating a record in the join table, but don't see why it isn't listing data from the first object in your found set. If you have fileMaker advanced, you might want to enable the script debugger and step through your script one step at a time and see if you can spot the problem. If you don't have advanced, you can try inserting some Show Custom dialog steps that pause your script and display the values of key fields and variables at that point and see if they give you a clue here.