9 Replies Latest reply on Aug 25, 2014 2:14 PM by sccardais

    Calculation to hold reasons record is omitted

    sccardais

      Title

      Calculation to hold reasons record is omitted

      Post

           I am working on a mailing list that contains records that need to omitted from a found set for one of several reasons.

           Some records meet more than on criteria for being omitted.

           I have a script that step sequentially through the reasons and sets a value in a field "Omit".

           I would like the "Omit" field to show all the reasons the record is being omitted. My current script replaces the value rather than appending the new reason (s) to the field.

           For example, if a particular record is missing an email address AND the user's access has been restricted. the Omit field should show, "Missing Email Address" , "Restricted Access"

           What's the best way to do this?

        • 1. Re: Calculation to hold reasons record is omitted
          philmodjunk

               Set Field can append data to the end of text already found in the field in a number of different ways.

               One way:

               Set Field [YourTable::OmitReason ; YourTable::OmitReason & ", " & "Restricted Address" ]

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: Calculation to hold reasons record is omitted
            sccardais

                 Not sure how to apply your idea on a record level.

                 I wrote a script that steps through 5 possible reasons for omitting records. After finding records that meet certain criteria, I "Replace" the value of Omit with Reason1, Reason2, etc.

                 The "Replace function is the problem (I know) but the Set Field function seems to work on the first record in the found set and not each.

                 For example, assume 12,000 records and 5 possible reasons. On my first pass, 50 records match Reason 1, so Omit is set to Reason1.

                 On the 2nd pass, 100 records match Reason 2. 50 of these ALSO matched Reason1 (they already have Reason1 in the Omit field. I want to append Reason2 to these records and set the value of Omit to Reason2 for the other 50.

                 The Set Field function didn't seem to do this. Does it require a Loop step in the script or ??

            • 3. Re: Calculation to hold reasons record is omitted
              sccardais

                   Follow Up -- could this be done using a calculation field in each record rather than a script?

              • 4. Re: Calculation to hold reasons record is omitted
                philmodjunk
                     

                          The "Replace function is the problem (I know) but the Set Field function seems to work on the first record in the found set and not each.

                     Correct, but the same calculation works with Replace Field Contents to append new data to the end of existing data in the field. Just use the part of the expression to the right of the semi-colon with replace field contents.

                • 5. Re: Calculation to hold reasons record is omitted
                  sccardais

                       I modified the script as shown on the attachment showing a portion of the script but it is not appending. It's replacing.

                       Is this because I need to add a Commit Records step or is there another reason?

                        

                       P.S. is there a way to copy script steps and paste into this comment box rather than having to create a screenshot to show scripts?

                  • 6. Re: Calculation to hold reasons record is omitted
                    philmodjunk

                         Your script checks the current record and then uses the contents of the OmitFlag field in that one record to select which method to use for Replace Field contents. One method does indeed overwrite existing data while the other appends.

                         Remove the If steps and just use the "append" version of Replace Field Contents as it will work equally well with records where the field is empty as it does with records where the field has data.

                         To post a script to the forum:

                           
                    1.           You can upload a screen shot of your script by using the Upload an Image controls located just below Post a New Answer.
                    2.      
                    3.           You can print a script to a PDF, open the PDF and then select and copy the script as text from the opened PDF to your clipboard for pasting here. (with this approach, you can get multiple script steps on the same line, please edit the pasted text by inserting some returns to separate those steps.)
                    4.      
                    5.           If You have FileMaker Advanced, you can generate a database design report and copy the script as text from there.
                    6.      
                    7.           If you paste a text form of the script, you can use the Script Pretty box in the Known Bugs List database to paste a version that is single spaced and indented for a more professional and easier to read format.
                    • 7. Re: Calculation to hold reasons record is omitted
                      sccardais

                           Thanks. That works great except for one minor thing. How do I get rid of the leading comma? See screenshot.

                           Some final step in the script to strip the leading comma and space?

                            

                      • 8. Re: Calculation to hold reasons record is omitted
                        philmodjunk

                             Hmm forgot about that detail. We can manage that with an If function built into the Replace calculation (instead of as a script step):

                             Replace Field Contents [ No Dialog ; Table::Omit Flag ; If ( Not IsEmpty ( Table::OmitFlag ) ; Table::OmitFlag & ", " ) & "Invalid Email"  ]

                        • 9. Re: Calculation to hold reasons record is omitted
                          sccardais

                               Phil:

                               Just a quick note to say Thank You. Your help is truly invaluable.