1 2 Previous Next 15 Replies Latest reply on Aug 14, 2012 12:05 PM by karendweaver

    Help with a script to add portal rows

    ebruml

      I have followed a vidoe to create a script that should automatically create a number of records in a portal once it has checked that the number of portal rows is not equal to 0.

      The script works except that it only creates the last row, eg only one row created instead of three. Any suggestions on how to fix very gratefully recieved.

       

      Loop

      If [contacts;;contact_type = "actor"]

      Go to Portal Row [Select; Last]

      Set Field [tasks::task; "contact actor"]

      Go to Portal Row [Select; Last]

      Set Field [tasks::task; "phone actor"]

      Go to Portal Row [Select; Last]

      Set Field [tasks::task; "contract for actor"]

      Else

      Go to Portal Row [Select; Last]

      Set Field [tasks::task; "contact writer"]

      End If

      Go to Record/Request/Page [Next; Exit after last]

      End Loop

        • 1. Re: Help with a script to add portal rows
          taylorsharpe

          Does the relationship allow the Creation of Records in the related portal table?

          • 2. Re: Help with a script to add portal rows
            ebruml

            Yes it does!

             

            From:  taylorsharpe <noreply@filemaker.com>

            Reply-To:  <fmi-1987201903-ulh-2-1yac@fmdev.filemaker.com>

            Date:  Friday, 10 August 2012 14:33

            To:  Eliza Bruml <brumlfamily@googlemail.com>

            Subject:  Re: Help with a script to add portal rows

             

            <https://fmdev.filemaker.com/index.jspa>

            created by taylorsharpe <https://fmdev.filemaker.com/people/taylorsharpe>

            in General - View the full discussion

            <https://fmdev.filemaker.com/message/91092#91092>

             

            Does the relationship allow the Creation of Records in the related portal

            table?

            Reply to this message by replying to this email -or- go to the message on

            FileMaker Technical Network

            <https://fmdev.filemaker.com/message/91092#91092> Start a new discussion in

            General by email

            <mailto:discussions-community-discuss-general@fmdev.filemaker.com>  or at

            FileMaker Technical Network

            <https://fmdev.filemaker.com/choose-container!input.jspa?contentType=1&conta

            inerType=14&container=2027>

                                    Manage your email preferences

            <https://fmdev.filemaker.com/index.jspa/user-notification-preferences!input.

            jspa> .

             

            FileMaker Developer Conference 2012 € Miami, Florida € July 16-19 €

            www.filemaker.com/devcon

            • 3. Re: Help with a script to add portal rows
              karendweaver

              Is there more than one portal on this layout?  If so, add a GoToObject script step to make sure you are in the right portal.  Is your portal sorted?  Is your relationship sorted? This can impact the "last row".   Are you using FMP Advanced - can you check for errors in the script debugger?

               

               

              If not, you can add a pause/resume after each script step so you can watch what is happening as you go.  And/or add a step to capture any errors and display them in a custom dialog after each step in your script.  Like this:

               

              Go to portal row, first

              set variable $error, Get ( LastError )

               

              If $error does not equal 0, Show custom dialog:  "Error number " &  $error & " , first portal row."

               

              repeat for each step, each portal row.

               

              That may provide a clue..  I recommend you upgrade to FileMaker Pro Advanced!

               

              Karen

              • 4. Re: Help with a script to add portal rows
                ebruml

                Thanks for your help I am already using FM Pro advanced 12. I will add the

                GoToObject script step. The relationship is not sorted.

                 

                From:  karendweaver <noreply@filemaker.com>

                Reply-To:  <fmi-1987201903-ulh-2-1yag@fmdev.filemaker.com>

                Date:  Friday, 10 August 2012 15:58

                To:  Eliza Bruml <brumlfamily@googlemail.com>

                Subject:  Re: Help with a script to add portal rows

                 

                <https://fmdev.filemaker.com/index.jspa>

                created by karendweaver <https://fmdev.filemaker.com/people/karendweaver>

                in General - View the full discussion

                <https://fmdev.filemaker.com/message/91096#91096>

                 

                Is there more than one portal on this layout?  If so, add a GoToObject

                script step to make sure you are in the right portal.  Is your portal

                sorted?  Is your relationship sorted? This can impact the "last row".   Are

                you using FMP Advanced - can you check for errors in the script debugger?

                If not, you can add a pause/resume after each script step so you can watch

                what is happening as you go.  And/or add a step to capture any errors and

                display them in a custom dialog after each step in your script.  Like this:

                Go to portal row, firstset variable $error, Get ( LastError ) If $error does

                not equal 0, Show custom dialog:  "Error number " &  $error & " , first

                portal row." repeat for each step, each portal row. That may provide a

                clue..  I recommend you upgrade to FileMaker Pro Advanced! Karen

                Reply to this message by replying to this email -or- go to the message on

                FileMaker Technical Network

                <https://fmdev.filemaker.com/message/91096#91096> Start a new discussion in

                General by email

                <mailto:discussions-community-discuss-general@fmdev.filemaker.com>  or at

                FileMaker Technical Network

                <https://fmdev.filemaker.com/choose-container!input.jspa?contentType=1&conta

                inerType=14&container=2027>

                                        Manage your email preferences

                <https://fmdev.filemaker.com/index.jspa/user-notification-preferences!input.

                jspa> .

                 

                FileMaker Developer Conference 2012 € Miami, Florida € July 16-19 €

                www.filemaker.com/devcon

                • 5. Re: Help with a script to add portal rows
                  karendweaver

                  Okay - what happens when you step through the script using the Script Debugger?

                   

                  Karen

                  • 6. Re: Help with a script to add portal rows
                    ebruml

                    Not sure if I am doing this correctly as it does not seem to throw an

                    error. I have attached the script for you to look at to see if you can see

                    an error. Basically  the script does the "else " bit fine and last bit of

                    the "if" , i.e. Only creates one new record in the portal row, instead of

                    the three required.

                    From:  karendweaver <noreply@filemaker.com>

                    Reply-To:  <fmi-1987201903-ulh-2-1yak@fmdev.filemaker.com>

                    Date:  Friday, 10 August 2012 16:21

                    To:  Eliza Bruml <brumlfamily@googlemail.com>

                    Subject:  Re: Help with a script to add portal rows

                     

                    <https://fmdev.filemaker.com/index.jspa>

                    created by karendweaver <https://fmdev.filemaker.com/people/karendweaver>

                    in General - View the full discussion

                    <https://fmdev.filemaker.com/message/91100#91100>

                     

                    Okay - what happens when you step through the script using the Script

                    Debugger? Karen

                    Reply to this message by replying to this email -or- go to the message on

                    FileMaker Technical Network

                    <https://fmdev.filemaker.com/message/91100#91100> Start a new discussion in

                    General by email

                    <mailto:discussions-community-discuss-general@fmdev.filemaker.com>  or at

                    FileMaker Technical Network

                    <https://fmdev.filemaker.com/choose-container!input.jspa?contentType=1&conta

                    inerType=14&container=2027>

                                            Manage your email preferences

                    <https://fmdev.filemaker.com/index.jspa/user-notification-preferences!input.

                    jspa> .

                     

                    FileMaker Developer Conference 2012 € Miami, Florida € July 16-19 €

                    www.filemaker.com/devcon

                    • 7. Re: Help with a script to add portal rows
                      karendweaver

                      Are there any script triggers on any of the fields or layouts?  What is the nature of the relationship Projects to ProjectAdmin?  What fields is the relationship based on?  I saw you have create related records on - if you manually create the records do you have any issues?

                       

                      I tested this on a simple sample database and it worked just fine, so I can only assume that there is something in the SeedCodeComplete that is blocking you.  Have you tried talking to SeedCode to see if they have any ideas why it isn't working? 

                       

                      It seems like maybe the relationship isn't immediately refreshing, so the system does not recognize the created record yet.  You could try a commit between each portal row to see if that forces an update.  But that is why I am asking about the relationship details.  Something isn't creating the record when you want it to.

                       

                      You might try creating a new layout with just one portal on it/  Create a new relationship based only on the key field of projects, then try your create record script on that portal.  That may give you some ideas about what the issue is.

                       

                      Karen

                      • 8. Re: Help with a script to add portal rows
                        ebruml

                        Thanks so much for all your suggestions to date. I think I need to change

                        the way I do this but desperately need a little bit of help with the

                        script. After I have checked that the record count in the portal is equal to

                        zero I think I need to do check the value of the Project Name and then

                        perform the relevant subscript.

                         

                        If the project name is "Bespoke Programme" then I need to perform this

                        script Bespoke Programme Admin Tasks (adding 3 records for example):

                        1. get the id for the lead

                        2. Go to the master layout for the admin type

                        3. Create a new record

                        4. Set the field for the action type to "Deposit invoice emailed"

                        5. Loop steps 3 and 4 to create more new records for "Consent form link

                        emailed" and "Staff booked"

                        If the project name is "Day Delegates" then I need to perform this script

                        Day Delegate Admin Tasks ( adding 4 records for example)

                        1. get the id for the lead

                        2. Go to the master layout for the admin type

                        3. Create a new record

                        4. Set the field for the action type to "xxxx"

                        5. Loop steps 3 and 4 to create more new records for "yyyy" and "zzz" and

                        "aaa"

                        If the project name is "Wedding" then I need to perform this script Wedding

                        Admin Tasks (adding 2 records for example)

                         

                        1. get the id for the lead

                        2. Go to the master layout for the admin type

                        3. Create a new record

                        4. Set the field for the action type to "xxxx"

                        5. Loop steps 3 and 4 to create more new records for "yyyy" and "zzz" and

                        "aaa"

                        At the end  I guess would need to exit the main loopŠ

                         

                        If you could provide any guidance on how I might write this script or some

                        reference that would help I would be very grateful.

                         

                         

                         

                         

                        From:  karendweaver <noreply@filemaker.com>

                        Reply-To:  <fmi-1987201903-ulh-2-1ybh@fmdev.filemaker.com>

                        Date:  Friday, 10 August 2012 23:24

                        To:  Eliza Bruml <brumlfamily@googlemail.com>

                        Subject:  Re: Help with a script to add portal rows

                         

                        <https://fmdev.filemaker.com/index.jspa>

                        created by karendweaver <https://fmdev.filemaker.com/people/karendweaver>

                        in General - View the full discussion

                        <https://fmdev.filemaker.com/message/91133#91133>

                         

                        Are there any script triggers on any of the fields or layouts?  What is the

                        nature of the relationship Projects to ProjectAdmin?  What fields is the

                        relationship based on?  I saw you have create related records on - if you

                        manually create the records do you have any issues? I tested this on a

                        simple sample database and it worked just fine, so I can only assume that

                        there is something in the SeedCodeComplete that is blocking you.  Have you

                        tried talking to SeedCode to see if they have any ideas why it isn't

                        working?   It seems like maybe the relationship isn't immediately

                        refreshing, so the system does not recognize the created record yet.  You

                        could try a commit between each portal row to see if that forces an update.

                        But that is why I am asking about the relationship details.  Something isn't

                        creating the record when you want it to. You might try creating a new layout

                        with just one portal on it/  Create a new relationship based only on the key

                        field of projects, then try your create record script on that portal.  That

                        may give you some ideas about what the issue is. Karen

                        Reply to this message by replying to this email -or- go to the message on

                        FileMaker Technical Network

                        <https://fmdev.filemaker.com/message/91133#91133> Start a new discussion in

                        General by email

                        <mailto:discussions-community-discuss-general@fmdev.filemaker.com>  or at

                        FileMaker Technical Network

                        <https://fmdev.filemaker.com/choose-container!input.jspa?contentType=1&conta

                        inerType=14&container=2027>

                                                Manage your email preferences

                        <https://fmdev.filemaker.com/index.jspa/user-notification-preferences!input.

                        jspa> .

                         

                        FileMaker Developer Conference 2012 € Miami, Florida € July 16-19 €

                        www.filemaker.com/devcon

                        • 9. Re: Help with a script to add portal rows
                          karendweaver

                          I am not sure what is the question you are asking about the new script.

                           

                          It sounds like you have a pretty good idea what to do.

                           

                          Using subscripts will make the main script easier.  So the main script loops through the found set, tests for the project type, then runs the appropriate subscript?  Sounds good.

                           

                          My suggestion to get you started, would be to build the subscripts first, then the main calling script with the loop.

                          Put the steps you've identified above in your scripts as comment lines, describing what you need to do.

                           

                          Then, manually step through the process with a few records - check to see that each step you take manually has a corresponding script step.  Think about error-trapping as you go - what happens if something breaks?  Add a comment to trap for the most common or critical errors.

                           

                          Then write your script steps, and test with a small number of records using the Script Debugger.

                           

                          Rinse, repeat until everything is working the way you want it.

                           

                          Karen

                          • 10. Re: Help with a script to add portal rows
                            tweller860

                            I haven't tested this with FM12, but in previous versions of FM Pro, I've had to use an "Insert Calculated Result" script step instead of the "Set Field" script step when working on records with in a portal object.  It would look something like this:

                             

                            #

                            Go to Object [ Object Name: "portalName" ]

                            #

                            Go to Portal Row [ Select ; Last]

                            Insert Calculated Result [ Select ; tasks::task ; "contact actor" ]

                            Go to Portal Row [ Select ; Last]

                            Insert Calculated Result [ Select ; tasks::task ; "phone actor" ]

                            Go to Portal Row [ Select ; Last]

                            Insert Calculated Result [ Select ; tasks::task ; "contract for actor" ]

                            #

                             

                            I'm usually only inserting a single value in a portal each time I go through a script.  You may need to add a "Commit Record" script step after each "Insert..." step.  Since you're already using FMPA 12, you'll be able to step through it and see exactlly if those extra "Commit" steps are needed.

                             

                            I hope you find this helpful.  Good luck.

                             

                            TW

                            • 11. Re: Help with a script to add portal rows
                              Claw'd

                              To simplify the script and remove duplicate effort the following may help:

                               

                              It can also be a help to place the text you want to insert into a global repeating field or better make a table with all the entries that you need to insert as records such as

                               

                              actor

                              contact actor

                              phone actor   

                              contract for actor

                              Each of these records would have a record No. or repetition No. So going with global repeaters

                               

                              Assuming go to object and selection last portal row and tab order selects portal fields in order

                              Then simply

                               

                              SetVariable($Var_Count) =1

                              Loop

                              SetField(Portal::Task;GetRepetition(TableOfText;$Var_Count)

                              GoToNextfield

                              GoToPortalRow(Last)

                              ExitLoopIF($Var_Count>3)

                              SetVariable($Var_Count) $Var_Count +1

                              EndLoop

                              CommitRecord()

                               

                              This gives flexibility in renaming the text you want inserting so it is not hard coded, with a bit more work one script can be used for all scenarios by passing a parrameter that contains the variable data.

                              While setting portal rows is one way creating records in the original table can be cleaner and faster depending how the portal relationship is set up.

                               

                              Not too criptic I hope

                               

                              Sample file attached

                              • 12. Re: Help with a script to add portal rows
                                ebruml

                                Thanks for all your help so far - I am quite a novice when it comes to scripting. However I have sucessfully managed to put together a script that works on all the records in the main found set of records without admin tasks: 

                                Create Admin Task Copy3


                                Go to Layout [ “Leads” (Leads) ]

                                 

                                #Find all leads with no admin task assigned

                                Perform Find [ Specified Find Requests: Find Records; Criteria: Leads::z_CountAdmin: “0” ]

                                [ Restore ]

                                Set Error Capture [ On ]

                                 

                                #If the found count is zero then exit the script

                                If [ Get ( FoundCount ) = 0 ]

                                Exit Script [ ]

                                End If

                                 

                                #Go the the first record in the found set of records that have no admin tasks created

                                Go to Record/Request/Page

                                [ First ]

                                 

                                #Set a variable for the lead ID, then create a new record for an admin task

                                 

                                Loop

                                Set Variable [ $leadID; Value:Leads::id_lead ]

                                Go to Layout [ “AdminTasks” (AdminTasks) ]

                                New Record/Request

                                Set Field [ AdminTasks::id_fk_lead; $leadID ]

                                Set Field [ AdminTasks::adminTask; "send deposit invoice" ]

                                New Record/Request

                                Set Field [ AdminTasks::id_fk_lead; $leadID ]

                                Set Field [ AdminTasks::adminTask; "send final invoice" ]

                                Go to Layout [ “Leads” (Leads) ]

                                Go to Record/Request/Page

                                [ Next; Exit after last ]

                                End Loop

                                Go to Layout [ “Leads” (Leads) ]

                                 

                                BUT whereever I put the if/end if statement for adding different tasks according to the value of the leadType field causes the script to fall over.

                                In plain english I need to

                                isolate the leads that have no associated admin tasks (as above)

                                then from that found set

                                find all those that have the value (for example) "Bespoke Porgramme"

                                complete the loop as above on this found set

                                return to the remaining records in the orginal found set

                                find all those that have the value (for example) "Day delegate"

                                complete a different loop on this found sub set

                                return to the remaining records in the orginal found set

                                find all those that have the value (for example) "Wedding"

                                complete a different loop on this found sub set

                                etc

                                 

                                Struggling on how to do this part now - any help most gratefully received.

                                • 13. Re: Help with a script to add portal rows
                                  karendweaver

                                  Okay, since you are new at this, lets make it less complicated!

                                   

                                  Why not break this script down into additional parts?

                                   

                                  You have a script where you find ALL leads with no Admin tasks, and then you have to do a lot of testing about which kind of lead it is - this can be done, but as you've discovered, it is really complicated to set up and get working correctly.  There's an easier way - just find each type and create their records in a subscript

                                   

                                  Write a calling script that does the following:

                                   

                                  Go to Layout [ “Leads” (Leads) ]

                                   

                                  #Find all "Bespoke Porgramme" leads with no admin task assigned

                                  Perform Find [ Specified Find Requests: Find Records; Criteria: Leads::z_CountAdmin: “0” ]

                                   

                                  *************

                                  AND has the value "Bespoke Porgramme"

                                   

                                  **************************

                                   

                                  [ Restore ]

                                  Set Error Capture [ On ]

                                   

                                  #If the found count is NOT zero then DO SUBSCRIPT the script

                                  If [ Get ( FoundCount ) > 0 ]

                                   

                                  #Go the the first record in the found set of records that have no admin tasks created

                                  Go to Record/Request/Page

                                  [ First ]

                                   

                                  #Set a variable for the lead ID, then create a new record for an admin task

                                   

                                  Loop

                                  DO SUBSCRIPT HERE - PERFORM SCRIPT:  CREATE BESPOKE RECORDS

                                      Subscript Steps: 

                                            Set Variable [ $leadID; Value:Leads::id_lead ]

                                            Go to Layout [ “AdminTasks” (AdminTasks) ]

                                            New Record/Request

                                            Set Field [ AdminTasks::id_fk_lead; $leadID ]

                                            Set Field [ AdminTasks::adminTask; "send deposit invoice" ]

                                            New Record/Request

                                            Set Field [ AdminTasks::id_fk_lead; $leadID ]

                                            Set Field [ AdminTasks::adminTask; "send final invoice" ]

                                            Go to Layout [ “Leads” (Leads) ]

                                            Go to Record/Request/Page

                                            [ Next; Exit after last ]

                                            End Loop

                                            Go to Layout [ “Leads” (Leads) ]

                                            EXIT SUBSCRIPT

                                   

                                   

                                   

                                   

                                  back to leads after subscript

                                  Go to Record/Request/Page

                                  [ Next; Exit after last ]

                                  End Loop

                                   

                                  THEN - do a FIND for the NEXT set of records and REPEAT

                                   

                                  #Find all "Day Delegate" leads with no admin task assigned

                                  Perform Find [ Specified Find Requests: Find Records; Criteria: Leads::z_CountAdmin: “0” ]

                                   

                                  *************

                                  AND has the value "Day Delegate"

                                   

                                  **************************

                                   

                                  [ Restore ]

                                  Set Error Capture [ On ]

                                   

                                  #If the found count is NOT zero then DO SUBSCRIPT the script

                                  If [ Get ( FoundCount ) > 0 ]

                                   

                                  #Go the the first record in the found set of records that have no admin tasks created

                                  Go to Record/Request/Page

                                  [ First ]

                                   

                                  Loop

                                   

                                  DO SUBSCRIPT HERE - CREATE DAY DELEGATE RECORDS

                                   

                                  return to leads after subscript

                                  Go to Layout [ “Leads” (Leads) ]

                                  Go to Record/Request/Page

                                  [ Next; Exit after last ]

                                  End Loop

                                  End If

                                   

                                  REPEAT until you have checked all records of the different types.

                                   

                                  Hope that helps!!!!

                                  • 14. Re: Help with a script to add portal rows
                                    ebruml

                                    Thanks so much for your reply.

                                    I completely understand the concept -

                                    Go the the layout "Leads"

                                    Find all the leads  with no admin tasks assigned and with the lead type

                                    "Bespoke Prog"

                                    If the found count>0 then go to the first of the records in this found set

                                    Perform the subscript that creates the new admin tasks record

                                     

                                    But I am struggling how to implement the loop steps.  I obviously need to

                                    loop through the records in the first found set what should be the loop

                                    structure here.

                                    At the end of the found set I need to go to all the remaining records

                                    Then I repeat the whole process for the second found set and call the next

                                    subscript.

                                     

                                    If you could just clarify how the looping works it would be really helpful.

                                    Many thanks

                                    Eliza

                                     

                                     

                                    From:  karendweaver <noreply@filemaker.com>

                                    Reply-To:  <fmi-891024484-ulh-2-1yjf@fmdev.filemaker.com>

                                    Date:  Tuesday, 14 August 2012 18:15

                                    To:  Eliza Bruml <brumlfamily@googlemail.com>

                                    Subject:  Re: Help with a script to add portal rows

                                    1 2 Previous Next