7 Replies Latest reply on Feb 4, 2017 3:04 PM by philmodjunk

    create related record in all records

    BKamp

      Hi guys,

       

      I have the following tables:

      • contactdetails
      • payouts (uitkeringen)
      • payouts temporary (tijdelijke_uitkeringen)

       

      contactdetails is the primary table, uitkeringen is related to it trough contactdetails pk. On the contactdetails layout I have a portal to payouts.

      payouts temporary is standalone, it is used to enter data that should be created in every contact's related record payout.

       

      so on the payouts temporary layout I have the same fields as in payouts portal on contactdetails. I want to populate them in payouts temporary and be able to press a button to run the following script that creates a payout for every contactperson in my db.

       

      Off course it's not working and I'm wondering why, when I press the button for the script, it just runs and does nothing until I press esc.

       

      here's the script:

       

      Set Variable [ $fonds; Value:tijdelijke_uitkeringen::fonds ]

      Set Variable [ $kwartaal; Value:tijdelijke_uitkeringen::kwartaal ]

      Set Variable [ $jaar; Value:tijdelijke_uitkeringen::jaar ]

      Set Variable [ $datum; Value:tijdelijke_uitkeringen::uitkeringsdatum ]

      Set Variable [ $koers; Value:tijdelijke_uitkeringen::uitkeringskoers ]

      Set Variable [ $percentage; Value:tijdelijke_uitkeringen::uitkeringspercentage ]

      Go to Layout [ “Contactdetails” (Contactpersonen) ]

      Go to Record/Request/Page [ First ]

      Loop

      #check if the member is a member of the fund that the uitkering was created for

      Go to Related Record [ From table: “Uitkeringen”; Using layout: “Uitkeringen” (Uitkeringen) ]

      New Record/Request
      Set Variable
      [ $error; Value:Get ( LastError ) ]
      Exit Loop If [ Get(LastError) <> 0 ]

      Set Field [ Uitkeringen::fonds; $fonds ]

      Set Variable [ $error; Value:Get ( LastError ) ]

      Exit Loop If [ Get(LastError) <> 0 ]

      Set Field [ Uitkeringen::kwartaal; $kwartaal ]

      Set Variable [ $error; Value:Get ( LastError ) ]

      Exit Loop If [ Get(LastError) <> 0 ]

      Set Field [ Uitkeringen::jaar; $jaar ]

      Set Variable [ $error; Value:Get ( LastError ) ]

      Exit Loop If [ Get(LastError) <> 0 ]

      Set Field [ Uitkeringen::uitkeringsdatum; $datum ]

      Set Variable [ $error; Value:Get ( LastError ) ]

      Exit Loop If [ Get(LastError) <> 0 ]

      Set Field [ Uitkeringen::uitkeringskoers; $koers ]

      Set Variable [ $error; Value:Get ( LastError ) ]

      Exit Loop If [ Get(LastError) <> 0 ]

      Set Field [ Uitkeringen::uitkeringspercentage; $percentage ]

      Set Variable [ $error; Value:Get ( LastError ) ]

      Exit Loop If [ Get(LastError) <> 0 ]

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

      End Loop

       

      Set Variable [ $error; Value:Get ( LastError ) ]

       

      If [ Get(LastError) <> 0 ]

      Revert Record/Request [ No dialog ]

      Show Custom Dialog [ Title: "Error"; Message: $error; Default Button: “OK”, Commit: “Yes”; Button 2: “Cancel”, Commit: “No” ]

      End If

       

        • 1. Re: create related record in all records
          philmodjunk

          You have several problems.

          If every record in contract details is to get a new related payment record, do not use go to related records. Use go to layout. Otherwise, your script will fail should this be the first payment record for a given contract detail.

          But the main problem in your script is that you need a go to layout step at the bottom of your loop to return to contract details before trying to go to the next record. This is why your script stops too soon.

           

          You also need to set a variable to the contract details pk before changing to the payment layout so that you can  use set field on the payment layout to link the new record to the contract details record.

           

          You might also web search the term "MagicKey" as this method would enable creatin payment records without changing layouts.

          1 of 1 people found this helpful
          • 2. Re: create related record in all records
            BKamp

            Perfect Phill, I now have a script that works.

            We have three funds, some people are not a member of let's say fund 1.

            The contactdetails table has a field for each fund with a boolean Yes or No for being a member. This is the 'Contactdetails::APP1 Deelname', 'Contactdetails::APP2 Deelname' and 'Contactdetails::APP3 Deelname' field.

             

            in my payouts temporary table I have a field called 'fund' where I select the fund, this is first variable that I set in my script ($fonds). Now I added a second variable called '$fonds_deelname' that is a dynamic var built from $fonds, (see below)

            At the start of the loop, I added an If statement to check of the current contactdetails record is a member of that fund, if not, skip. This way the payout only gets added to the participants of the specific fund, not to all persons.

             

            However, whenever I add this If statement, no records get added at all, when I remove it, the payout gets added to all contactdetail records.

             

            Why is it not passing the if statement?:

             

            btw, I get and 101 error when the loop is done every time I debug the script, missing record although I added the 'exit after last' part.

             

             

            Set Variable [ $fonds; Value:tijdelijke_uitkeringen::fonds ]

            Set Variable [ $fonds_deelname; Value:"Contactpersonen::" & $fonds & " Deelname" ]

            Set Variable [ $kwartaal; Value:tijdelijke_uitkeringen::kwartaal ]

            Set Variable [ $jaar; Value:tijdelijke_uitkeringen::jaar ]

            Set Variable [ $datum; Value:tijdelijke_uitkeringen::uitkeringsdatum ]

            Set Variable [ $koers; Value:tijdelijke_uitkeringen::uitkeringskoers ]

            Set Variable [ $percentage; Value:tijdelijke_uitkeringen::uitkeringspercentage ]

            Go to Layout [ “Contactdetails” (Contactpersonen) ]

            Go to Record/Request/Page [ First ]

            Loop

            #check if the member is a member of the fund that the uitkering was created for

            If [ $fonds_deelname = "Ja" ]


            Set Variable [ $contactID; Value:Contactpersonen::__pkContactID ] Go to Layout [ “Uitkeringen” (Uitkeringen) ]
            New Record/Request
            Set Variable
            [ $error; Value:Get ( LastError ) ]
            Exit Loop If [ Get(LastError) <> 0 ]
            Set Field [ Uitkeringen::_fkContactID; $contactID ]
            Set Variable [ $error; Value:Get ( LastError ) ]
            Exit Loop If [ Get(LastError) <> 0 ]
            Set Field [ Uitkeringen::fonds; $fonds ]
            Set Variable [ $error; Value:Get ( LastError ) ]
            Exit Loop If [ Get(LastError) <> 0 ]
            Set Field [ Uitkeringen::kwartaal; $kwartaal ]
            Set Variable [ $error; Value:Get ( LastError ) ]
            Exit Loop If [ Get(LastError) <> 0 ]
            Set Field [ Uitkeringen::jaar; $jaar ]
            Set Variable [ $error; Value:Get ( LastError ) ]
            Exit Loop If [ Get(LastError) <> 0 ]
            Set Field [ Uitkeringen::uitkeringsdatum; $datum ]
            Set Variable [ $error; Value:Get ( LastError ) ]
            Exit Loop If [ Get(LastError) <> 0 ]
            Set Field [ Uitkeringen::uitkeringskoers; $koers ]
            Set Variable [ $error; Value:Get ( LastError ) ]
            Exit Loop If [ Get(LastError) <> 0 ]
            Set Field [ Uitkeringen::uitkeringspercentage; $percentage ]
            Set Variable [ $error; Value:Get ( LastError ) ]
            Exit Loop If [ Get(LastError) <> 0 ]

             

            End If

             

            Go to Layout [ “Contactdetails” (Contactpersonen) ]

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

            End Loop

             

            Set Variable [ $error; Value:Get ( LastError ) ]

             

            If [ Get(LastError) <> 0 ]

            Revert Record/Request [ No dialog ]

            Show Custom Dialog [ Title: "Error"; Message: $error; Default Button: “OK”, Commit: “Yes”; Button 2: “Cancel”, Commit: “No” ]

            End If

             

            • 3. Re: create related record in all records
              philmodjunk

              "Contactpersonen::" & $fonds & " Deelname" the value that you assign to $fonds_deelname will never have the value "Ja".

              • 4. Re: create related record in all records
                BKamp

                I'm not sure I get what you're trying to say here.

                 

                'Contactpersonen:APP1 Deelname' (for example) has the following calculation:

                 

                If ( IsEmpty ( APP1::Deelnamedatum ) ; "Nee" ; "Ja" )

                 

                meaning it either has the value "Nee" or "Ja"

                • 5. Re: create related record in all records
                  philmodjunk

                  The second line of your script assigns a value to the variable $fonds_deelname.

                  The value is assigned with the expression:

                   

                  "Contactpersonen::" & $fonds & " Deelname"

                   

                  This does not evaluate as you think. The first part of this variable's value will always be the text "Contactpersonen::". This will be followed by the value of $Fonds with " Deelname" text appended to the end. This text will never, can never be the text "Ja".

                   

                  Presumably you are trying to calculate the name of a field in order to assign the value of that field to $Fonds_Deelname, but that is not what the calculation does.

                   

                  If that's what you want here, you need: GetField ( "Contactpersonen::" & $fonds & " Deelname" )

                   

                  Note that this name has to be exactly right or GetField will return a null value. It's also not the best approach as a change of table occurrence name (Contactpersonen::) or field name ( $fonds & " Deelname" ) will cause your script to fail. There are other ways to set up this kind of indirect field referencing and perhaps in this case you need a related table of such values instead of a different field for each one all in the same record.

                   

                  But even then, you only calculate this value a single time. So your script will either generate related records for all records in your found set or none of them.

                  • 6. Re: create related record in all records
                    BKamp

                    Thanks Phill, that did the trick!

                    I'm aware of the fact that the script will fail if the name changes or whatsoever. I have full control and overview of this db am am sure the fund name will stay the same.

                    So this is my easiest approach, agree?

                    • 7. Re: create related record in all records
                      philmodjunk

                      I couldn't truly agree or disagree as I don't know enough about your solution to offer that kind of opinion.

                       

                      But I would not use indirect field referencing like you have as I would not want to use a method that breaks with a name change even if I were the only developer.