1 2 Previous Next 16 Replies Latest reply on May 31, 2012 3:07 PM by philmodjunk

    Need way to update records in mass

    TomPleickhardt

      Title

      Need way to update records in mass

      Post

      If I have three databases. 1. list of names, 2. List of email campaigns, 3. Connects multi to multi of names to campaigns. I need a way to find names in the database and update all those records with the new campaign. Can that be done?

        • 1. Re: Need way to update records in mass
          philmodjunk

          If I understand correctly what you mean by "update all those records with the new campaign", I think you want to find a set of records in 1, then create a join record in 3 that links them to a specified record in 2.

          It can be done, but the details depend on the design of your database.

          By "databases" do you mean tables or files?

          How have you defined the relationships between these three? (what fields are used to match values? What relationship options have you selected?)

          • 2. Re: Need way to update records in mass
            TomPleickhardt

            Attached is the database design. My use case is as follows.. I have a list of names that I send letters. Each letter is a campaign (Name and Date). I send letters out about every 60 days. So I search for mailed dates older than 60 days (I don't know if I do that well). I use the list of names with the portal view of the campaign name and date. I then export the names to an excel file to a mail house.

            I regularly import new names, so the only way I know who I sent each mailer is through the excel file. I need to update the database with those people I sent each campaign. Is there an easier way to mark all the records that I send to each time? Let me know if I explained well enough.

             

            Tom

            • 3. Re: Need way to update records in mass
              philmodjunk

              First, what you have is a many to many relationship made possible by using Lead_Campaign as a join table such that a record in Lead_Campaign links a specific record in campaign to a specific record in Import.

              The first step is fairly straight forward and you appear to be able to do that:

              Find the records in your Import table to which you intend to send out this mailing.

              Step two isn't fully clear as you have various ways you could accomplish this:

              Select the Campaign record for the mailing you intend to send out to them.

              This could be the current record on a layout based on Campaign because you have just created the new Campaign record. You could select the campaign_ID in a drop down list set on a global field. You could put a portal listing all campaign records on your Import layout and clicking a row in the portal selects that campaign. Let me know what method makes sense to you in terms of your layout design and work flow and I'll provide more details on this part.

              Whatever method you use, put the Campaign_ID into a variable: Set Variable [ $$CampID ; value: Campaign::Campaign_ID]

              That takes us to step 3: Create one new record in Lead_Campaign to link each record in the found set of records in Import to the selected campaign record.

              On your Import layout where you have performed your find to find the records you want in this table, perform a looping script like this:

              #Script must be performed from layout based on Import table
              Freeze Window
              Go To Record/Request/Page [First]
              Loop
                 Set Variable [$CustID ; value: Import::CustomerID]
                 Go to Layout [Lead_campaign]
                 New Record/Request
                 Set Field [Lead_campaign::CustomerID ; $CustID]
                 Set Field [Lead_campaign::campaignID ; $$CampID]
                 Go to layout [Import]
                 Go to Record/Request/Page [next ; exit after last]
              End Loop

              • 4. Re: Need way to update records in mass
                TomPleickhardt

                I like put a portal listing all campaign records on your Import layout and clicking a row in the portal selects that campaign for step 2. I am extremely rusty and basically new to FMPro12. Any specifics would be entirely helpful and completely appreciated.

                • 5. Re: Need way to update records in mass
                  philmodjunk

                  What I am suggesting works the same way with FileMaker 11 and older versions as long as they support using $Variables.

                  First, we need a new occurrence of Campaign in order to set up that portal.

                  In Manage | Database | relationships, make a new table occurrence of Campaign by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box as AllCampaigns.

                  We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

                  Add it to your relationships like this:

                  Import::anyField X AllCampaigns::anyField

                  Drag from any field in Import to any field in AllCampaign, then double click the relationship line this action creates to open a dialog box where you can chnage the = operator to the cartesian Join (x) operator. Now you can put a portal to AllCampaigns on a layout based on Import.

                  Put the fields you want to see in this portal. Then, while in layout mode, select all the fields and use button setup to turn them into a button that performs this script:

                  #Script must be performed from layout based on Import table
                  Set Variable [$CampID ; Value: AllCampaigns::CampaignID ]
                  Freeze Window
                  Go To Record/Request/Page [First]
                  Loop
                     Set Variable [$CustID ; value: Import::CustomerID]
                     Go to Layout [Lead_campaign]
                     New Record/Request
                     Set Field [Lead_campaign::CustomerID ; $CustID]
                     Set Field [Lead_campaign::campaignID ; $CampID]
                     Go to layout [Import]
                     Go to Record/Request/Page [next ; exit after last]
                  End Loop

                  • 6. Re: Need way to update records in mass
                    TomPleickhardt

                    So I ran into a couple of issues. In the loop the first set Field cant be done (I don't think). Here is the snapshot. the CustomerID is not a field available. Did I do it wrong? I didn't go further. Thank you for your help!

                     

                    • 7. Re: Need way to update records in mass
                      philmodjunk

                      When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

                      • 8. Re: Need way to update records in mass
                        TomPleickhardt

                        Sorry, maybe I wasn't very clear. The first set field you want me to create uses a field "CustomerID" that doesn't exist in that table "Lead Campaign. There are only 3 fields in that table (Lead_Campaign_ID, Lead_ID and Campaign_ID). My question was about your reference to Customer_ID in that table. Was that a mistake or am I doing it wrong?

                        • 9. Re: Need way to update records in mass
                          TomPleickhardt

                          Sorry, maybe I wasn't very clear. The first set field you want me to create uses a field "CustomerID" that doesn't exist in that table "Lead Campaign. There are only 3 fields in that table (Lead_Campaign_ID, Lead_ID and Campaign_ID). My question was about your reference to Customer_ID in that table. Was that a mistake or am I doing it wrong?

                           

                          Here is as close as I could do per your instructions. I used teh Contact Detail as the layout with the portal you described. That layout is referenced in the second to last line in your script. The main problem is on the first Set Field that you instructed me to create. Please let me know what to do.

                          • 10. Re: Need way to update records in mass
                            philmodjunk

                            Sorry,

                            Set Field [Lead_campaign::CustomerID ; $CustID]

                            Should be:

                            Set Field [Lead_campaign::Lead ID ; $CustID]

                            • 11. Re: Need way to update records in mass
                              TomPleickhardt

                              Two questions:

                              How do I use this new function?

                              1) search for the record criteria

                              2) run script?

                              3)?

                               

                              Second,

                              Since I didn't have a way of doing my updates, I have an excel file export that I did not that long ago, but dont remember the search criteria, so all I have to work from is the excel file. Is there a way to update those records in the database which match those in the excel file (based on Name and address I guess) and ad that mail campain history similar to what the new function you have been helping me with?

                               

                              Thank you very much in advance!

                              • 12. Re: Need way to update records in mass
                                philmodjunk

                                How do I use this new function?

                                Did you do this? "Put the fields you want to see in this portal. Then, while in layout mode, select all the fields and use button setup to turn them into a button that performs this script:"

                                Create the new campaign record

                                Find the records in Import that you want to link to the new campaign

                                Click the button in the portal to launch the script (and this mouse click selects the campaign you want to which to link them.)

                                On further thought, you may want to modify this script to use Show Custom Dialog to aske the user if they really want to do this so you have a way to bail out if you accidentally click the wrong button.

                                Second,

                                Since I didn't have a way of doing my updates,...

                                It's not impossible to do, but may or may not be easy. You can use Import records to pull data from the excel file into tables in FileMaker, but you don't have the ID values to use to link the records--you'd need to figure out a way to supply that missing data. Using addresses to help match to existing records in your Import table is a possiblity, but only if you don't have duplicate addresses or addresses that are nearly the same but different in some small way such as an extra space in the field.

                                • 13. Re: Need way to update records in mass
                                  TomPleickhardt

                                  I tried that. The database didnt act as expected. Is there a way to send a copy with no records to you so you can see what I did wrong? I use drop box or other large file free tools if that helps..

                                  • 14. Re: Need way to update records in mass
                                    philmodjunk

                                    I suggest clicking Edit on your last post and removing your email address. There are folks that use software to "scrape" email addresses from public forums like this so you could end up on the receiving end of a lot of spam....

                                    If you have a drop box account you can use save a copy to save a clone (empty of records) to your drop box and then post the download link here. When you do so, please describe what you mean by "Didn't act as expected" so I know what to look for when looking at your file.

                                    1 2 Previous Next