5 Replies Latest reply on Dec 27, 2011 9:30 AM by Sprucegum

    Count of fields with same value per record

    MikeEdwards

      Title

      Count of fields with same value per record

      Post

      Hi,

      I attempting to create a calculation field which will offer a count of ten different fields which share a value list which have the same value for that record.  If I have a value list of 4 values 1. vacation 2. jury duty, 3. fmla and 4. bereavement. can I get a running count of how vacation days selected out of the 10 fields for that record?

        • 1. Re: Count of fields with same value per record
          LaRetta_1

          Hi Mike,

          "...create a calculation field which will offer a count of ten different fields"

          The odds are very great that those ten different fields should be records in a related table.  If they were records, a simple sub-summary report would do the job.  Whenever you have several 'like' fields in a table, be concerned about your setup.  You have (probably) hit the first such roadblock using this structure; there will be many more.  If instead, you use a related table, each record would have a Type field to hold Vacation, Jury Duty etc.  Then you have the simple task of summing the days.

          I strongly suggest that you consider alternatives before you get much further. If you can create a simple example of your structure, or if you feel you can share your file, please upload it (empty clone) and provide a link here.  If you wish, you can contact us through private message and we can take a look back channel.  If structured properly, your work will become far easier.

          • 2. Re: Count of fields with same value per record
            MikeEdwards

             Thank You LeRetta

            Let me consider that approach but in the mean time I've considered a different tact. When a value is selected from a valuer list in a field (such as vacation" it would deduct rather than count the available vacation days field for the record.

            I do like your suggestion and this DB is still in it's infacy however I've always prefered real time data as opposed to reporting.

            your thoughts?

            • 3. Re: Count of fields with same value per record
              Sprucegum

              Hi Mike,

              You could use a Let function to concatinate the values in fields 1-10, then use PatternCount to count specific occurences (replace f1-f10 below with your field references).  If your value list never changes, for example, you could use:

                    Let(vl=f1 & ¶ & f2 & ¶ & f3 & ¶ & f4 & ¶ & f5 & ¶ & f6 & ¶ & f7 & ¶ & f8 & ¶ & f9 & ¶ & f10;

                          PatternCount ( vl; "Vacation"))

              If your value list changes periodically and other calculation fields rely on the result of this field, you may want the search value for PatternCount to be a variable, global or other field (instead of the literal text "Vacation").

              OR, if you just want to display an occurence count for each value in the value list, you could use something like:

                    Let(vl=f1 & ¶ & f2 & ¶ & f3 & ¶ & f4 & ¶ & f5 & ¶ & f6 & ¶ & f7 & ¶ & f8 & ¶ & f9 & ¶ & f10;

                          PatternCount ( vl; GetValue(ValueListItems ( Get(FileName); "ValueList"); 1)) & " " &

                                GetValue(ValueListItems ( Get(FileName); "ValueList"); 1) & ¶ &
                          PatternCount ( vl; GetValue(ValueListItems ( Get(FileName); "ValueList"); 2)) & " " &

                                GetValue(ValueListItems ( Get(FileName); "ValueList"); 2) & ¶ &
                          PatternCount ( vl; GetValue(ValueListItems ( Get(FileName); "ValueList"); 3)) & " " &

                                GetValue(ValueListItems ( Get(FileName); "ValueList"); 3) & ¶ &
                          PatternCount ( vl; GetValue(ValueListItems ( Get(FileName); "ValueList"); 4)) & " " &

                                GetValue(ValueListItems ( Get(FileName); "ValueList"); 4))

              S

              • 4. Re: Count of fields with same value per record
                LaRetta_1

                Hi Mike,

                My thoughts?  Reporting is the small benefit.  The large benefit is ease of design and simplicity.  If you do not go the true relational route, you had better get very good at things such as the solution proposed by Steve.

                Change it now, Mike, while you can.  I can assure you that, down the road after you have flattened your forehead completely, you will have to change it anyway.  Even if you DO happen to succeed after much torment and thousands of needless calculations, it will run like a dog and sink like a stone.

                LaRetta

                • 5. Re: Count of fields with same value per record
                  Sprucegum

                  Hi Mike & LaRetta,

                  LaRetta's absolutely right, of course, and I echo her recommendation (I posted before seeing her comments) ... structuring a database well in the first place will help avoid countless hours of work-arounds (or work-around work-arounds).

                  I wish I'd known how to better structure a database years ago (v.3), but at the time I was less interested in learning how than immediate resluts.

                  Best wishes to you both,

                  S