7 Replies Latest reply on Mar 6, 2017 3:16 PM by philmodjunk

    sorting for report using multiple criteria

    Jwaldridge

      OK Guys I am new to this so forgive the ineloquent scripting. There scenario is as follows...

       

      Our customers can sign up for a Service to be provided Annually, Bi-Anually, Tri-Anually (new word ) and quarterly.

      I have created a form that gives a start date and produces a set of service dates depending on which option they choose.  see script below...

       

      Screen Shot 2017-03-06 at 10.26.14 AM.png

      now i have 1-4 fields with dates in them.  I do not care about the specific date, only the month, so i have it only showing me the months.

      I have also created a global field called "Service Agreement::Service Month" I would like to create a form that looks at Service agreement::Service Month and returns info on customers if that month is located in any of the 4 date fields.

       

      Any suggestions where to start?

        • 1. Re: sorting for report using multiple criteria
          philmodjunk

          Back up and change both your script and your data model.

           

          SA Date 1, SA Date 2, SA Date 3, SA Date 4 should be replaced by a single date field placed in a related table such  that you have 4 (or however many you might need) related records instead of these fields. That will make reporting much simpler.

           

          Then, use Set Field instead of Insert Calculated result in your scripts. Insert Calculated result only works if the field is present on the current layout and can trip script triggers due to setting the focus on the specified field. Set Field does not have those issues.

          • 2. Re: sorting for report using multiple criteria
            Jwaldridge

            I am not sure the best way to go about your first recommendation.

            I currently create an agreement and put a start date, and type and that populates the 4 fields. The key is to keep the entry process easy, I don't want to have to change 4 entries any time i need to make a change to the customers start month. Am I understanding you incorrectly? 

            Also how do you calculate the months if you are using Set Field instead of calculate?

            My inngle field would be start date,  can you give me  an example of how too use set field to populate  SA- date 1 if annually and so on?

             

            Thanks for the help sorry i am not understanding,

            • 3. Re: sorting for report using multiple criteria
              philmodjunk

              Using a set of related records--often displayed in a portal, need not make the data entry any more difficult and it will make reporting on these dates MUCH simpler.

              I currently create an agreement and put a start date, and type and that populates the 4 fields.

              A script trigger on the start date field can perform a script to create and/or update such a set of related records and thus you woul d not need to change 4 entries manually every time you update that start date field.

              • 4. Re: sorting for report using multiple criteria
                Jwaldridge

                OK I think I have done what you recommend on the creation however i am not seeing an easy way to have them delete or change.  here is the script.

                 

                 

                Screen Shot 2017-03-06 at 1.50.26 PM.png

                 

                I know you cannot see the end of the script but it basically repeats itself.

                 

                I end up with 4 different portal entries if I choose quarterly however if i change the start date or the type (annually , bi-annually) i would meet the script to delete the existing portal records and start over or replace the existing records with the new date. 

                 

                Screen Shot 2017-03-06 at 1.53.24 PM.png

                • 5. Re: sorting for report using multiple criteria
                  philmodjunk

                  I suggest taking a Look at MagicKey--you can web search for this as a way to create and modify records in a related table. It's named "magic" for a reason as there's a bit of "smoke and mirrors" feel to it when you first start using it, but it's very useful as you don't have to change layouts  in order to create/modify related records. You can use the List function to get a list of ID's from the related table (IDs that uniquely identify each record in the related table not the customer) and you loop through them if you want to update related records.

                   

                  But you can also use Go to Related Records to find the set of records linked to a given customer. You could then delete them all and create new ones or loop through them and update the values.

                   

                  I mention that option as MagicKey can't be used to actually delete related records. But you can use MagicKey to "disconnect" them by clearing the match field (SA2::Name in the above script). You'd then run a script periodically to find and delete all such records where this field is empty to "clean up" the table.

                   

                  PS. I wouldn't match related table by name like this either. Names are not always unique and customers--even companies change their names--which then screws up the links to related data. So if SA::Name stores a name, you should change that so that you can match by an auto-enterd serial number or UUID that never changes even if the customer's name changes.

                  • 6. Re: sorting for report using multiple criteria
                    Jwaldridge

                    Thanks you for all your help.  i will look into the multiple areas you recommend, and post an update when I am done.  You have been a huge help

                    • 7. Re: sorting for report using multiple criteria
                      philmodjunk

                      You might look at:

                       

                      Script running in portal

                       

                      as I just posted a scripted example of how MagicKey can be used to create related records. It's a simpler issue than you have here, but the techniques apply.