10 Replies Latest reply on Jul 31, 2013 1:28 AM by willrollo

    creating new records from found set

      Title

      creating new records from found set

      Post

           I have created a list of customers that Have been sent a letter. When this letter has been printed, various fields (date printed ) etc are filled so I know when the customer was sent a letter. However, I have also created a 'To Do' table. This works by creating a new reminder record that has fields such as ToDo:name text, date due etc.

           What I wanrt to be able to do is when I click print,  I want a dialogue to appear asking if a reminder is required. Yes/No. 

           If Yes, then the found set (those customers selected for printing) will now have a new related To Do record stating the name of the customer/text, due date etc.

           Ihave got it half working - the main issue is that I cannot get the related customer info (name) into the new reminder record, and only one record is being created. I am inepxrienced with the 'loop' expression and unsure if this is what is required here. I have apsted my attempt below...dodgy bit highlighted in red....thank you

            

             
      •           Perform Find [ Specified Find Requests: Find Records; Criteria: Customers::Mailmerge checkbox: “Y” ] [ Restore ]
      •      
      •           Go to Related Record [ From table: “Customers”; Using layout: “Preview (mail merge)” (Customers) ] [ Show only related records; Match found set ]
      •      
      •           Print Setup [ Orientation: Portrait; Paper size: 8.26" x 11.69" ] [ Restore; No dialog ]
      •      
      •           Enter Preview Mode [ Pause ]
      •      
      •           Print [ Records being browsed; All Pages; Orientation: Landscape; Paper size: 8.26" x 11.69" ] [ Restore: Kitchen Printer; No dialog ]
      •      
      •           Set Field [ Customers::Follow_Up_Date_Old; Customers::Follow_Up_Date_Recent ]
      •      
      •           Set Field [ Customers::Follow_Up_Date_Recent; Get(CurrentDate) ]
      •      
      •           Set Field [ Customers::Follow Up Actions Box; "Mail Merge Sent" ]
      •      
      •           Set Field [ Customers::Recent Contact; Customers::Follow up letter type ]
      •      
      •           Enter Browse Mode
      •      
      •           #Set Reminder
      •      
      •           Show Custom Dialog [ Title: "Message"; Message: "Do you want to set a reminder to follow up this mail merge in two weeks? "; Default Button: “Yes”, Commit: “Yes”; Button 2: “No”, Commit: “No” ]
      •      
      •           If [ Get(LastMessageChoice) = 1 ]
      •      
      •           Set Variable [ $ID_Contact; Value:INVOICE DETAILS::ID_Customer_Billing_fk ]
      •      
      •           Set Variable [ $note; Value:Customers::Follow up letter type ]
      •      
      •           Freeze Window
      •      
      •           Go to Layout [ “TO DO” (contacts_TO DO) ]
      •      
      •           Go to Record/Request/Page [ First ]
      •      
      •           New Record/Request
      •      
      •           Set Field [ contacts_TO DO::ID_Customers_fk; $ID_Contact ]
      •      
      •           Set Field [ contacts_TO DO::Name_To Do; Customers::Address To ]
      •      
      •           Set Field [ contacts_TO DO::Date Due; Get(CurrentDate)+14 ]
      •      
      •           Set Field [ contacts_TO DO::Text; $Note ]
      •      
      •           Commit Records/Requests [ Skip data entry validation; No dialog ]
      •      
      •           Go to Layout [ “ContactList” (Customers) ]
      •      
      •           End If
      •      
      •           If [ Get(LastMessageChoice) = 3 ]
      •      
      •           Exit Script [ ]
      •      
      •           End If
      •      
      •           Go to Layout [ “ContactList” (Customers) ]

        • 1. Re: creating new records from found set
          shilpas@metasyssoftware.com
                 
          •           Perform Find [ Specified Find Requests: Find Records; Criteria: Customers::Mailmerge checkbox: “Y” ] [ Restore ]
          •      
          •           Go to Related Record [ From table: “Customers”; Using layout: “Preview (mail merge)” (Customers) ] [ Show only related records; Match found set ]
          •      
          •           Print Setup [ Orientation: Portrait; Paper size: 8.26" x 11.69" ] [ Restore; No dialog ]
          •      
          •           Enter Preview Mode [ Pause ]
          •      
          •           Print [ Records being browsed; All Pages; Orientation: Landscape; Paper size: 8.26" x 11.69" ] [ Restore: Kitchen Printer; No dialog ]
          •      
          •           Set Field [ Customers::Follow_Up_Date_Old; Customers::Follow_Up_Date_Recent ]
          •      
          •           Set Field [ Customers::Follow_Up_Date_Recent; Get(CurrentDate) ]
          •      
          •           Set Field [ Customers::Follow Up Actions Box; "Mail Merge Sent" ]
          •      
          •           Set Field [ Customers::Recent Contact; Customers::Follow up letter type ]
          •      
          •           Enter Browse Mode
          •      
          •           #Set Reminder
          •      
          •           Show Custom Dialog [ Title: "Message"; Message: "Do you want to set a reminder to follow up this mail merge in two weeks? "; Default Button: “Yes”, Commit: “Yes”; Button 2: “No”, Commit: “No” ]
          •      
          •           If [ Get(LastMessageChoice) = 1 ]
          •      
          •           //Let us navigate to “ContactList” (Customers) layout from "Preview (mail merge)(Customers)"
          •      
          •           Go to Layout [ “ContactList” (Customers) ]
          •      
          •           Go To record/Request/page [First]
          •      
          •           Set Variable [ $ID_Contact; Value:Customers::Id_Customer]
          •      
          •           Set Variable [ $note; Value:Customers::Follow up letter type ]
          •      
          •           Freeze Window
          •      
          •           Loop
          •      
          •           Go to Layout [ “TO DO” (contacts_TO DO) ]
          •      
          •           Go to Record/Request/Page [ First ]
          •      
          •           New Record/Request
          •      
          •           Set Field [ contacts_TO DO::ID_Customers_fk; $ID_Contact ]
          •      
          •           Set Field [ contacts_TO DO::Name_To Do; Customers::Address To ]
          •      
          •           Set Field [ contacts_TO DO::Date Due; Get(CurrentDate)+14 ]
          •      
          •           Set Field [ contacts_TO DO::Text; $Note ]
          •      
          •           Commit Records/Requests [ Skip data entry validation; No dialog ]
          •      
          •           Go to Layout [ “ContactList” (Customers) ]
          •      
          •           Go To record/Request/page[Next;Exit After last]
          •      
          •           End loop
          •      
          •           End If
          •      
          •           If [ Get(LastMessageChoice) = 3 ]
          •      
          •           Exit Script [ ]
          •      
          •           End If
          •      
          •           Go to Layout [ “ContactList” (Customers) ]
          • 2. Re: creating new records from found set

                 Hi MetaSys

                 Thank you very much for the detailed solution - it works perfectly except for one thing - The Set Field [ contacts_TO DO::Name_To Do; Customers::Address To ]   Provides the same data on each record (The name from the first record in Customers that is in the found set)

                 As this is the only field in the record that is different from the rest of the new records, then the issue may related to all those set fields although it onlt matters for the name...

                 So there are the correct amount of new ToDo records, just with all the same customer name...Any ideas?

                  

                 Huge thanks

            • 3. Re: creating new records from found set
              shilpas@metasyssoftware.com

                   Assuming following is your database structure:

                   Table: Customers
                   Fields:
                   ID_Customer
                   Address To
                   Follow up letter type

                   Table: To Do
                   Fields
                   ID_ToDo (primary key)
                   FK_ID_Customer (foreign key)
                   Name_To_Do
                   Text

                   Set a parent child relationship between Customers & To Do table. In 'To Do' table, define 'Name_To_Do' and 'Text' fields as auto enter calculation fetching values of customer table.

                   Alternative is store the customers::Address_to, in variable similar to $Note and set it into ToDo::name_To_Do field. Following is script modified

                                    
              •                          Perform Find [ Specified Find Requests: Find Records; Criteria: Customers::Mailmerge checkbox: “Y” ] [ Restore ]
              •                     
              •                          Go to Related Record [ From table: “Customers”; Using layout: “Preview (mail merge)” (Customers) ] [ Show only related records; Match found set ]
              •                     
              •                          Print Setup [ Orientation: Portrait; Paper size: 8.26" x 11.69" ] [ Restore; No dialog ]
              •                     
              •                          Enter Preview Mode [ Pause ]
              •                     
              •                          Print [ Records being browsed; All Pages; Orientation: Landscape; Paper size: 8.26" x 11.69" ] [ Restore: Kitchen Printer; No dialog ]
              •                     
              •                          Set Field [ Customers::Follow_Up_Date_Old; Customers::Follow_Up_Date_Recent ]
              •                     
              •                          Set Field [ Customers::Follow_Up_Date_Recent; Get(CurrentDate) ]
              •                     
              •                          Set Field [ Customers::Follow Up Actions Box; "Mail Merge Sent" ]
              •                     
              •                          Set Field [ Customers::Recent Contact; Customers::Follow up letter type ]
              •                     
              •                          Enter Browse Mode
              •                     
              •                          #Set Reminder
              •                     
              •                          Show Custom Dialog [ Title: "Message"; Message: "Do you want to set a reminder to follow up this mail merge in two weeks? "; Default Button: “Yes”, Commit: “Yes”; Button 2: “No”, Commit: “No” ]
              •                     
              •                          If [ Get(LastMessageChoice) = 1 ]
              •                     
              •                          //Let us navigate to “ContactList” (Customers) layout from "Preview (mail merge)(Customers)"
              •                     
              •                          Go to Layout [ “ContactList” (Customers) ]
              •                     
              •                          Go To record/Request/page [First]
              •                     
              •                          Set Variable [ $ID_Contact; Value:Customers::Id_Customer]
              •                     
              •                          Set Variable [ $NameToDo; Value:Customers::Address To]
              •                     
              •                          Set Variable [ $note; Value:Customers::Follow up letter type ]
              •                     
              •                          Freeze Window
              •                     
              •                          Loop
              •                     
              •                          Go to Layout [ “TO DO” (contacts_TO DO) ]
              •                     
              •                          Go to Record/Request/Page [ First ]
              •                     
              •                          New Record/Request
              •                     
              •                          Set Field [ contacts_TO DO::ID_Customers_fk; $ID_Contact ]
              •                     
              •                          Set Field [ contacts_TO DO::Name_To Do; $NameToDo ]
              •                     
              •                          Set Field [ contacts_TO DO::Date Due; Get(CurrentDate)+14 ]
              •                     
              •                          Set Field [ contacts_TO DO::Text; $Note ]
              •                     
              •                          Commit Records/Requests [ Skip data entry validation; No dialog ]
              •                     
              •                          Go to Layout [ “ContactList” (Customers) ]
              •                     
              •                          Go To record/Request/page[Next;Exit After last]
              •                     
              •                          End loop
              •                     
              •                          End If
              •                     
              •                          If [ Get(LastMessageChoice) = 3 ]
              •                     
              •                          Exit Script [ ]
              •                     
              •                          End If
              •                     
              •                          Go to Layout [ “ContactList” (Customers) ]
              •                

                    

              • 4. Re: creating new records from found set

                     Dear Meta Sys. I already had the relationshop set up (Customers ID=>TO DO CustomersFK). I have tried the auto enter method and also the variables. No difference though... At a loss! Think maybe something not quite right with the way it goes to the next record to create a To Do as it jsut seems to fill the to do records with the same, first customer record details...

                • 5. Re: creating new records from found set
                  philmodjunk

                       Sounds like you are missing this step That should precede the Go To REcord step.

                  Go to Layout [ “ContactList” (Customers) ]

                       The field names seem mismatched: Customers::Address To, contacts_TO DO::Name_To Do

                       But that does not mean this shouldn't work, it depends one what actual data is supposed to be in them. You also don't actually need to copy the data in to this field unless you need to keep a copy of the data in Contacts_TO DO that does not actually change when the data in Customers with the same ID value is modified. If the relationship between Customers and Contacts_TO DO is valid, Any layout or portal that lists records from Contacts_TO DO can include Address To from a related occurrence of Customers.

                  • 6. Re: creating new records from found set

                         Hi Phil

                         Sorry, I Cant see where this is missing as it appears that the line Go to Layout [ “ContactList” (Customers) ] has been added before both instances of Go To Record...or am I missing something?

                         I will alter the mismatched names as a little confusing...once it works! I can then remove the set fields that will autofill due to the related nature of the tables...

                    • 7. Re: creating new records from found set
                      philmodjunk

                           Can you post the latest version of your script? Small details can be critical here. You can make your posted script easier to read if you use the PrettyPrint field in the Known Bugs List Database. It will indent steps that are inside a an If or Loop block and thus make the script eaier to read.

                      • 8. Re: creating new records from found set

                             Sure - here is the complete script - 

                              

                               
                        •           Perform Find [ Specified Find Requests: Find Records; Criteria: Customers::Mailmerge checkbox: “Y” ] [ Restore ]
                        •      
                        •           Go to Related Record [ From table: “Customers”; Using layout: “Preview (mail merge)” (Customers) ] [ Show only related records; Match found set ]
                        •      
                        •           Print Setup [ Orientation: Portrait; Paper size: 8.26" x 11.69" ] [ Restore; No dialog ]
                        •      
                        •           Enter Preview Mode [ Pause ]
                        •      
                        •           Print [ Records being browsed; All Pages; Orientation: Landscape; Paper size: 8.26" x 11.69" ] [ Restore: Kitchen Printer; No dialog ]
                        •      
                        •           Set Field [ Customers::Follow_Up_Date_Old; Customers::Follow_Up_Date_Recent ]
                        •      
                        •           Set Field [ Customers::Follow_Up_Date_Recent; Get(CurrentDate) ]
                        •      
                        •           Set Field [ Customers::Follow Up Actions Box; "Mail Merge Sent" ]
                        •      
                        •           Set Field [ Customers::Recent Contact; Customers::Follow up letter type ]
                        •      
                        •           Enter Browse Mode
                        •      
                        •           #Set Reminder
                        •      
                        •           Show Custom Dialog [ Title: "Message"; Message: "Do you want to set a reminder to follow up this mail merge in two weeks? "; Default Button: “Yes”, Commit: “Yes”; Button 2: “No”, Commit: “No” ]
                        •      
                        •           If [ Get(LastMessageChoice) = 1 ]
                        •      
                        •           Go to Layout [ “ContactList” (Customers) ]
                        •      
                        •           Go to Record/Request/Page [ First ]
                        •      
                        •           Set Variable [ $ID_Contact; Value:Customers::ID_Contact_pk ]
                        •      
                        •           Set Variable [ $Address; Value:Customers::Address To ]
                        •      
                        •           Set Variable [ $note; Value:"Customer was sent " & Customers::Follow up letter type & " and needs following up. " ]
                        •      
                        •           Freeze Window
                        •      
                        •           Loop
                        •      
                        •           Go to Layout [ “TO DO” (Customers_TO DO) ]
                        •      
                        •           New Record/Request
                        •      
                        •           Set Field [ Customers_TO DO::ID_Customers_fk; $ID_Contact ]
                        •      
                        •           Set Field [ Customers_TO DO::Name_To Do; $Address ]
                        •      
                        •           Set Field [ Customers_TO DO::Date Due; Get(CurrentDate)+14 ]
                        •      
                        •           Set Field [ Customers_TO DO::Text; $Note ]
                        •      
                        •           Commit Records/Requests [ Skip data entry validation; No dialog ]
                        •      
                        •           Go to Layout [ “ContactList” (Customers) ]
                        •      
                        •           Go to Record/Request/Page [ Next; Exit after last ]
                        •      
                        •           End Loop
                        •      
                        •           End If
                        •      
                        •           If [ Get(LastMessageChoice) = 3 ]
                        •      
                        •           Exit Script [ ]
                        •      
                        •           End If
                        •      
                        •           Go to Layout [ “ContactList” (Customers) ]
                        •      
                        •           Show All Records
                        •      
                        •           Go to Record/Request/Page [ First
                        • 9. Re: creating new records from found set
                          philmodjunk

                               It's a bit easier to read formatted like this:

                               I believe the changes you need to make are shown as strike through text where the steps should be removed and in Blue where they should be added.

                               Perform Find [ Specified Find Requests: Find Records; Criteria: Customers::Mailmerge checkbox: “Y” ] [ Restore ]
                               Go to Related Record [ From table: “Customers”; Using layout: “Preview (mail merge)” (Customers) ] [ Show only related records; Match found set ]
                               Print Setup [ Orientation: Portrait; Paper size: 8.26" x 11.69" ] [ Restore; No dialog ]
                               Enter Preview Mode [ Pause ]
                               Print [ Records being browsed; All Pages; Orientation: Landscape; Paper size: 8.26" x 11.69" ] [ Restore: Kitchen Printer; No dialog ]
                               Set Field [ Customers::Follow_Up_Date_Old; Customers::Follow_Up_Date_Recent ]
                               Set Field [ Customers::Follow_Up_Date_Recent; Get(CurrentDate) ]
                               Set Field [ Customers::Follow Up Actions Box; "Mail Merge Sent" ]
                               Set Field [ Customers::Recent Contact; Customers::Follow up letter type ]
                               Enter Browse Mode
                               #Set Reminder
                               Show Custom Dialog [ Title: "Message"; Message: "Do you want to set a reminder to follow up this mail merge in two weeks? "; Default Button: “Yes”, Commit: “Yes”; Button 2: “No”, Commit: “No” ]
                               If [ Get(LastMessageChoice) = 1 ]
                                   Go to Layout [ “ContactList” (Customers) ]
                                   Go to Record/Request/Page [ First ]
                              Set Variable [ $ID_Contact; Value:Customers::ID_Contact_pk ]
                                   Set Variable [ $Address; Value:Customers::Address To ]

                                   Set Variable [ $note; Value:"Customer was sent " & Customers::Follow up letter type & " and needs following up. " ]
                                   Freeze Window
                                   Loop
                                 Set Variable [ $ID_Contact; Value:Customers::ID_Contact_pk ]
                                      Set Variable [ $Address; Value:Customers::Address To

                                       Go to Layout [ “TO DO” (Customers_TO DO) ]
                                       New Record/Request
                                       Set Field [ Customers_TO DO::ID_Customers_fk; $ID_Contact ]
                                       Set Field [ Customers_TO DO::Name_To Do; $Address ]
                                       Set Field [ Customers_TO DO::Date Due; Get(CurrentDate)+14 ]
                                       Set Field [ Customers_TO DO::Text; $Note ]
                                       Commit Records/Requests [ Skip data entry validation; No dialog ]
                                       Go to Layout [ “ContactList” (Customers) ]
                                       Go to Record/Request/Page [ Next; Exit after last ]
                                   End Loop
                          End If
                          Else If [ Get(LastMessageChoice) = 32 ]
                                   Exit Script [ ]
                               End If
                               Go to Layout [ “ContactList” (Customers) ]
                               Show All Records
                               Go to Record/Request/Page [ First
                                

                          • 10. Re: creating new records from found set

                                 Thank you so much - It works perfectly...