11 Replies Latest reply on Apr 3, 2013 3:41 AM by Brucey

    Duplicate records with customised error message

    Brucey

      Title

      Duplicate records with customised error message

      Post

            

           Hi Everyone
            
           I need to make a ‘Create Subs’ button to automatically generate the members subs records for a given year. when the button is clicked:
            
           A check should be carried out to see if the members subs records for the given year already exist, and if they do, a customised error message should be displayed (eg if the year 2011 was being viewed and the button was clicked a customised error message would be generated to state that the records already exist)
            
           If the members subs records for the given year do not exist they should be created (eg clicking the ‘Create Subs’ button would lead to the creation of a member’s subs record for each member for the year 2013)
            
           The Fees for 2013 are as follows
            
           Full Fee                £45.00
           Junior Fee            £10.00
           Honorary Fee       £0.00
            
           Hope this makes sense.  Any help appreciated.

        • 1. Re: Duplicate records with customised error message
          philmodjunk

               Do you have a "Members" table with one record for each member and a "members subs" table with one record for each member's subscription for a given year?

               How are they related? What fields are defined in Members Subs?

               You should have something like this for your relationships:

               Members----<Members Subs

               Members::__pkMemberID = Members Subs::_fkMemberID

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

               If so, you can create a new Table Occurrence of Members Subs and link it to Members Subs lke this:

               Members Subs|Year-----Members----<Members Subs

               Members::__pkMemberID = Members Subs|Year::_fkMemberID AND
               Members::gYear = Members Subs|Year::Year

               gYear would be a number field with global storage specified.

               Then a reference to IsEmpty ( Members Subs|Year::_fkMemberID ) will evaluate as True only if a Members Subs record for the year specified in gYear does not exist.u

               There are two variations of this that you can consider:

               1) If you matched fields only by year and gYear, IsEmpty() would not be true if any member Sub records for that year have been created. (The current member record might be for a member that didn't renew.)

               2) If you enabled "Allow creation of Records via this relationship" for Members Subs|Year for the first version that matches by ID and by year, this script step:

               Set Field [Members Subs|Year::_fkMemberID ; Members::__pkMemberID]

               If performed from a record on a Members layout, will create a new record for the current member and designated year, if such a record does not already exist. If such a record exists for that member, no change to your data takes plae.

          • 2. Re: Duplicate records with customised error message
            Brucey

                 Hi PhilModjunk

                 Here is the set up of my tables and relationships.  I just can't seem to get the script to work for what i am trying to do.

                  

                  

                  

            • 3. Re: Duplicate records with customised error message
              Brucey

                   Hi PhilModjunk

                   Here is the set up of my tables and relationships.  I just can't seem to get the script to work for what i am trying to do.

                    

                    

                    

              • 4. Re: Duplicate records with customised error message
                philmodjunk

                     Please note that your relationships match my initial description of your relationships, but does not match the suggested addition to these relationships that you can use to check for the existance of a given member Sub record.

                     I'm a bit puzzled by the function of YearlySubs. There's no field there to identify the year and it's not clear to me how the record in YearlySubs can define the correct fee for a given member sub record.

                • 5. Re: Duplicate records with customised error message
                  Brucey

                       See image of YearlySubs_tbl and the calculation used in the MemberSubs_tbl how work out the fee for a particular member fee.

                        

                  • 6. Re: Duplicate records with customised error message
                    philmodjunk

                         Then the relationship change that I recommended can be used to check for the existence of a member sub record for a specific member/year. Using your table occurrence and field names, the relationship would look like this:

                         Members::membershipNumber_pk = MemberSubs|Year::membership_Number_fk AND
                         Members::gYear = MemberSubs|Year::Subscription_year_fk

                         MemberSubs|Year would be the name of a new Tutorial: What are Table Occurrences? that refers to MemberSubs.

                    • 7. Re: Duplicate records with customised error message
                      Brucey

                           Hi Phil

                           Thanks for your time with this. I am having problems getting my head around your proposed solution. I have created the following script to find duplicate records.

                           As you can see, the way it works is when I put the year 2010, 2011 or 2012 into the Subscription_Year_fk field an error message is displayed. That works fine.  It is the bit after this I cannot work out. I want the script to create seven new blank records in this case. (This is the number of members) If there were ten members, I would want it to create ten new blank records etc. What I did not mention in my first post was that the two honorary members should automatically have their subs marked as paid. See below what the table will then look like.

                           Once again, thanks for your help with this.

                      • 8. Re: Duplicate records with customised error message
                        Brucey

                             Hi Phil

                             Thanks for your time with this. I am having problems getting my head around your proposed solution. I have created the following script to find duplicate records.

                              

                             As you can see, the way it works is when I put the year 2010, 2011 or 2012 into the Subscription_Year_fk field an error message is displayed. That works fine.  It is the bit after this I cannot work out. I want the script to create seven new blank records in this case. (This is the number of members) If there were ten members, I would want it to create ten new blank records etc. What I did not mention in my first post was that the two honorary members should automatically have their subs marked as paid. See below what the table will then look like.

                              

                             Once again, thanks for your help with this.

                        • 9. Re: Duplicate records with customised error message
                          Brucey

                               Table

                                

                          • 10. Re: Duplicate records with customised error message
                            philmodjunk

                                 Your script isn't what I recommended. I suggested checking for the existance of related records in MemberSubs from the context of a related table.

                                 Example: You can go to a layout for YearlySubs and perform your find there. If a record is found, use Not IsEmpty ( memberSubs::MemberShip_number_fk ) to determine if memberSub records for that year exist or not.

                                 If no such records exist or no YearlySub reocrd exists, your script can create the new YearlySub Record if needed and then it should change layouts, pull up a found set or member records and loop through them, creating a new record for each in MemberSubs. This requires using the Loop, endLoop and Go to Record/Request/Page [next ; exit after last] steps.

                            • 11. Re: Duplicate records with customised error message
                              Brucey

                                   Hi Phil

                                   Thanks for your time and help with this. I used the loop, endLoop and Go to Record/Request/Page [next ; exit after last] steps etc, refined it some more and it works fine now.

                                   Cheers

                                   Brucey