7 Replies Latest reply on Jan 8, 2017 12:28 PM by srbridges

    Is there a way to prevent duplicate entries in scripted data entry?

    srbridges

      I have 3 tables; Contacts, Affiliations and Donations. (All related through a ContactID field.)

       

      I have portals (in popover windows) on the Contacts home page showing affiliations and donations for that particular contact.

       

      I have a separate popover window that creates a new affiliation using global fields. All that works fine.

      However I want to make sure each contact has no more that one affiliation per year. Is there a way to do that? I would like to perform the check before I commit the record, but am not sure I can do that.

       

      Any ideas?

       

       

      Thanks,

       

      Here is the script to create New Affiliation

      Screen Shot 2016-11-19 at 8.23.41 PM.png

        • 1. Re: Is there a way to prevent duplicate entries in scripted data entry?
          taylorsharpe

          Sure, just test it with SQL before creating a new record.  In other words, do the popover BEFORE your New Record/Request script step. 

           

          Set Variable [ $Test =

                         Let ( [

                              AF = Resources::gAff ;

                              YR = Resources::gMemYr ;

                              SQL = "SELECT

                                        COUNT ( * )

                                   FROM

                                        contacts_AFFILIATION

                                   WHERE

                                        Affiliation = ? and

                                        MembershipYr = ?" ;

                              Result = ExecuteSQL ( SQL ; ¶ ; ¶ ; AF ; YR ) ;

                              Result = GetAsNumber ( Result  )

                              ] ;  Result ) ]

           

          Then do an If Statement that tests if $Test > 0, then it already exists and do whatever you want in that case. 

          • 2. Re: Is there a way to prevent duplicate entries in scripted data entry?
            taylorsharpe

            After thinking about it, create a script trigger on the Popover that does this test on Popover Exit and keep the popover from closing until they have a unique Affiliation and Year or else give them an option to cancel. 

            1 of 1 people found this helpful
            • 3. Re: Is there a way to prevent duplicate entries in scripted data entry?
              srbridges

              Thank you Taylor for your suggestion. I have to confess that I have never used SQL before so I will have to read up on how to do that. Your second suggestion about using script trigger on Popover Exit raises some questions too… I think I read somewhere that I need to somehow use a variable to determine when to run the on exit script since I think the popover closes when I navigate to new layout to create record… but I am not sure how to do that either..

              Thanksgiving preparations have kept me occupied for the past week so it will be next week before I can spend any time on this, but I hope you will continue to give me advice when I get back to this!

               

              Susie

              • 4. Re: Is there a way to prevent duplicate entries in scripted data entry?
                philmodjunk

                A relationship can be used to ensure that only one such record exists for a given year and contact

                 

                You can enable the "create" option on the following relationship:

                 

                DataEntryTable::gContactID = Affiliations::_fkContactID AND

                DataEntryTable::gYear = Affiliations::Year

                 

                You can use set field to set data to a field in Affiliations to create a new record with the contact ID and year specified in the two global fields. IsEmpty ( Affiliations::_fkContactID ) will tell you if such a record already exists and this can be used to keep from creating a new affiliations for the current contact and year.

                1 of 1 people found this helpful
                • 5. Re: Is there a way to prevent duplicate entries in scripted data entry?
                  Malcolm

                  You are created the record then testing for the presence of the affiliation. Why don't you do the test first?

                   

                   

                  if [ valueCount ( filtervalues ( contacts_affiliation::membershipYear  ; resources::gblMbrYr ) ) ]

                  ... record exists

                  else

                  ... proceed

                  end

                  • 6. Re: Is there a way to prevent duplicate entries in scripted data entry?
                    philmodjunk

                    srbridges has indicted that he/she is using globals so no new record is created at first. I'd put an indicator inside that popover that tells the user that the affiliation record exists so that they can save themselves the trouble of even filling out the global field.

                    • 7. Re: Is there a way to prevent duplicate entries in scripted data entry?
                      srbridges

                      This method seemed the simplest and worked for me. I also used the same technique to test for existing affiliation before allowing for a donation to be entered in a new year. Thanks for your help in pointing out a function that I had never used!