5 Replies Latest reply on Aug 1, 2017 6:38 PM by philmodjunk

    Cumulative numbering

    tays01s

      I have 3 tables: A, B, C.

      A pk < B_Afk

      B pk < C_Bfk &

      A Bn < C Bfk where the Bn is set from a Button script.

       

      Field from table C are in a Portal on layout from A.

       

      Q: If have a C_audit field with a 0/1 checkbox. I'd like to have the record order to appear in another field using the C_audit field with which to calculate the order. It is possible users may delete portal records other than the last one so the 'order' field will have to recalculate.

        • 1. Re: Cumulative numbering
          philmodjunk

          You can use the insert menu to insert text that shows the record number.  This will always show the row number in perfect sequence as long as you get this text Object fully into the portal row--which can require some careful resizing of the text object. (Drag the portal a few pixels and this text object should move with it. )

           

          A running total summary field that either counts a never empty field or sums a field that always has the value 1 can also be used in contexts outside the bounds of a portal.

          • 2. Re: Cumulative numbering
            tays01s

            Ultimately this will go into a runtime so I'm assuming the former is not so useful.

             

            I'd tried the latter but with 2 problems:

            1. When I check/ uncheck the field that 'scores' 1 if audited, I'm told that the field is not modifiable (though actually it does modify) and

            2. The running total includes a record from a different patient even though the running score field is from a TO using the relationship: A Bn < C Bfk

            that should restrict records to only the active Table B::uuID.

             

            BTW, the field that is being summed has blank or 1; is blank OK or does it need to be 0 or 1?

            • 3. Re: Cumulative numbering
              philmodjunk

              A) both methods should slso work in a run time

              B) the summary fields would sum a field that always has the value 1. It would not be used to sum a field that is 1 on one record and zero for another.

              C) neither method renders another data field in modifiable  

              D) the running total summary field will "number" only the records in the portal or currently in a found set if not used in a portal so including a record for another patient makes no sense unless that record is also in the portal.

              • 4. Re: Cumulative numbering
                tays01s

                Your comment B) means this won't work then. The 'Audit?' field has to be either 1 or either 0 or "". It can't always be '1' because some of these records won't be included in the audit.

                 

                Script: I found a solution by using a script trigger on the 'Audit ?' field that sets the 'Audit Day' field to the 'sum(Audit ?)' where the 'Audit ?' is found from a relationship where 'Audit date' has to be >= 'Audit date' of the TO from which Audit ? is summed.

                 

                So far seems to work.

                • 5. Re: Cumulative numbering
                  philmodjunk

                  "Your comment B) means this won't work then. The 'Audit?' field has to be either 1 or either 0 or "". It can't always be '1' because some of these records won't be included in the audit."

                   

                  And where did I tell you to sum the "Audit?" Field?

                   

                  You can always sum a different field that is always 1.

                   

                  That said, summing that field would work if your found set or portal only has records where the field is 1.