1 2 Previous Next 16 Replies Latest reply on Dec 28, 2016 1:34 PM by BruceRobertson

    Exclude a record from report

    sansae

      Hello,

      I found this thread from 2013:

      Want to exclude a record with empty fields from a report

       

      I'm trying to do the same thing. I took the suggestion of using Constrain Found Set[], but my script isn't working as expected.

      What I want is to exclude from my report any records that have an invalid zipcode field. That is to say, a zipcode that does not have 5 numbers (e.g. 12345). There is a script function called "isEmpty", which I'm sure I can use to successfully exclude records whose zip fields are empty, but what if the zip field contains a space, " ", or carriage return, etc. (note: there are several records in my db that have such values in the zip field; i'm not supposed to delete those records, but rather ignore them in my report).

       

      Here's what I have:

      Set Variable [ $ListZips ; Value: List ( table_occurrence :: zip_field) ]

      Constrain Found Set [ Restore ]

       

      Restore has this field:

      table_occurrence :: zip_field

       

      and this criteria:

      $ListZips = "#####"

       

      In Data Viewer, $ListZips shows me my zipcodes.

      My thinking is that I can constrain that set of zipcodes with the criteria of "#####", or 5 numbers (which represent a valid US zipcode).

      However, this doesn't work and I am getting the "no records match this find criteria" error.

       

      I just want to exclude/ignore/"make disappear from my found set (but not from the database)" records that do not have a valid zipcode.

       

      Is there a script function that will allow me to exclude such records?

      Can anyone help me?

       

      Message was edited by: San Sae added more info

        • 1. Re: Exclude a record from report
          erolst

          I'm not sure why you use that variable; you can simply constrain the found set to records with a five digit zip like this:

           

          Enter Find Mode [ pause off ]

          Set Field [ zip_field ; "==#####" ]

          Constrain Found Set [ no restore ]

          • 2. Re: Exclude a record from report
            sansae

            Thank you for the response, erolst.

             

            erolst wrote:

             

            I'm not sure why you use that variable

            for my own learning purposes I guess. I've been using data viewer extensively for the past week and it's helped me out a lot. I'm a very visual learner and I really have to see what's going on "behind the scenes" to understand it. I learn better that way. There's also another reason, but I can't think of it right now. I don't know. My head hurts. Moving on...

             

            erolst wrote:

             

            Enter Find Mode [ pause off ]

            Set Field [ zip_field ; "==#####" ]

            Constrain Found Set [ no restore ]

            Enter find mode...

            Hmmm. If I were using a layout that showed me all my zipcodes maybe??? If that were the case, I could definitely enter find mode, set the zip field, then constrain my found set, but I'm not using a layout that shows me all those records. I'm on a layout that contains one record, and from that one record, I'm able to "see" all those records in question.

             

            Let me think about this one some more. Sorry. I'm so not able to think straight right now.

            Thanks again though. Will report back later.

            • 3. Re: Exclude a record from report
              philmodjunk

              As long as the layout is based on the correct table occurrence, enter find mode[], Set field, Perform find will work. If your layout is not based on the correct table occurrence, no version of a find--either scripted or manual will work.

              • 4. Re: Exclude a record from report
                BruceRobertson
                I'm on a layout that contains one record, and from that one record, I'm able to "see" all those records in question.

                That is very strange statement. What does it mean so "see" many records where the layout only "contains" one record?

                Maybe you are on a layout which includes a portal?

                Maybe you mean a form view; where you only see one record at a time, though you have a found set of N records?

                • 5. Re: Exclude a record from report
                  sansae

                  BruceRobertson wrote:

                   

                  That is very strange statement. What does it mean so "see" many records where the layout only "contains" one record?

                  Maybe you are on a layout which includes a portal?

                  Maybe you mean a form view; where you only see one record at a time, though you have a found set of N records?

                  After re-reading my own statement, I would have to agree, Bruce. Strange indeed.

                   

                  I meant a form view.

                  On this form view is a "record" that contains various fields with edit boxes, dropdowns, and checkboxes as their control styles. User selects certain values from these fields, then clicks on a button to generate a lovely report based on the selections. I create a magical script to help with that.

                   

                  As always, thank you for your response. Your questions to my questions are helping me learn this fm stuff. Really tough so far btw.

                  • 6. Re: Exclude a record from report
                    erolst

                    sansae wrote:

                    User selects certain values from these fields, then clicks on a button to generate a lovely report based on the selections. I create a magical script to help with that.

                     

                    As always, thank you for your response. Your questions to my questions are helping me learn this fm stuff. Really tough so far btw.

                    That's all very touching , but didn't you start this thread with the question of how to exclude certain records from - presumably - that report to - one assumes - make it even lovelier?

                     

                    Or is this about presenting only valid zipcodes as choices? If you still want an answer to the original question, you need to describe your scenario in (more) detail.

                    • 7. Re: Exclude a record from report
                      BruceRobertson

                      Your replies are still extremely confused and confusing.

                      You are apparently not distinguishing between a report-configuration record; and the many data records which should be included when a report specified by this configuration is run.

                      1 of 1 people found this helpful
                      • 8. Re: Exclude a record from report
                        sansae

                        BruceRobertson wrote:

                         

                        Your replies are still extremely confused and confusing.

                        [insert blushing emoji]

                         

                        BruceRobertson wrote:

                         

                        You are apparently not distinguishing between a report-configuration record; and the many data records which should be included when a report specified by this configuration is run.

                         

                        I think that's it actually. I have a layout with a report-configuration record and not an actual "data" record. (Please correct me if that statement is either a. confusing, b. inaccurate, or c. both).

                         

                        report-configuration record vs. data record

                         

                        Two different things? Got it.

                        If I'm wrong yet again, please do correct me.

                         

                        Anyway, I've figured out a solution to my problem.

                        Thank you, again, Bruce.

                        • 9. Re: Exclude a record from report
                          BruceRobertson

                          When running the report, the find operations described by Phil will do what you said you are looking for.

                          Is that the solution you settled on?

                          If not - what does the solution look like?

                          • 10. Re: Exclude a record from report
                            sansae

                            erolst wrote:

                            That's all very touching ,

                             

                            I'm very sentimental.

                             

                            erolst wrote:

                            didn't you start this thread with the question of how to exclude certain records from - presumably - that report to - one assumes - make it even lovelier?

                             

                            Too many breaks in your sentence. I got lost reading it.

                            I'd give it another go in responding to your "record"-related question, but I'm afraid Bruce might throw another one of his blunt knives at me again. haha. Just kidding, of course. That's my way of saying I don't know how to answer that question without making a full of myself. Apparently, I still don't know what a record is. I think I'm getting closer though.

                             

                            erolst wrote:

                            Or is this about presenting only valid zipcodes as choices? If you still want an answer to the original question, you need to describe your scenario in (more) detail.

                            This is about getting information from only valid zipcodes, yes. But in order to get that info, I need those zipcodes. In my case, or at least at the moment, I only care about presenting the user with information for zipcodes that aren't empty (i.e. it can have digits or letters, or even happy faces). The user selects a date range, department, and service type, then clicks on a report button to get said information.

                             

                            I found a solution to my problem though. Thanks, erolst.

                            • 11. Re: Exclude a record from report
                              sansae

                              BruceRobertson wrote:

                              When running the report, the find operations described by Phil will do what you said you are looking for.

                              Yeah... I guess for my situation, using a find operation doesn't work either because a. it truly doesn't or b. I don't know how to use it in this specific script that I'm working on. If I'm correct, the find operation only works if you are using a layout that have fields you can actually perform a find on. In my case, there are no such fields. I attempted this find operation in my script, and none of my fields are searchable.

                               

                              BruceRobertson wrote:

                              Is that the solution you settled on?

                              If not - what does the solution look like?

                              I created an "if the current zipcode is empty, then move on to the next zipcode" statement. I nested this inside a loop.

                               

                              In my original post, I referenced the isEmpty() function:

                              "There is a script function called "isEmpty", which I'm sure I can use to successfully exclude records whose zip fields are empty, but what if the zip field contains a space, " ", or carriage return, etc."

                               

                              My mistake was not trying out that function to begin with before posting. I was concerned that it wasn't going to work since there were zipcodes in my db whose "values" were an empty space or a carriage return. In my testing, I learned that isEmpty() considers both as true.

                               

                              Hope that wasn't confusing.

                              • 12. Re: Exclude a record from report
                                BruceRobertson

                                since there were zipcodes in my db whose "values" were an empty space or a carriage return. In my testing, I learned that isEmpty() considers both as true.

                                 

                                Hope that wasn't confusing.

                                Oh well.

                                The isEmpty function does NOT consider a return; or a space; to be empty.

                                • 13. Re: Exclude a record from report
                                  BruceRobertson

                                  Here's a mod to an example from a previous discussion with you, that adds the constrain operation described by erolst.

                                  1 of 1 people found this helpful
                                  • 14. Re: Exclude a record from report
                                    sansae

                                    BruceRobertson wrote:

                                    The isEmpty function does NOT consider a return; or a space; to be empty.

                                    In that case, I was correct in my thinking prior to actually using the function. Spaces and carriage returns aren't "empty" according to isEmpty.

                                    Thanks for the heads-up.

                                     

                                    I went ahead and used it anyway and got my script to work the way I wanted. Report now generates nicely. I wonder why my script worked the way it did. It skipped through the zipcodes that were "blank" (but really, they contained either a space or carriage return).

                                    Hmm...

                                    Not sure why.

                                     

                                    It says here: IsEmpty

                                    that isEmpty() returns a number.

                                    I'm guessing that when isEmpty() is used to check a value, it's looking for a number and if it doesn't find one, it returns 1, or true (maybe I shouldn't even use the word true; that's probably inaccurage, hahaha). Returns and spaces are not numbers, but they're not "empty" either. Got it.

                                     

                                    I think the confusion is that I don't know what "empty" means. (sighs).

                                    Aaaaaaaaahhhh!!!!!!!!!

                                    1 2 Previous Next