13 Replies Latest reply on Feb 8, 2012 1:09 PM by philmodjunk

    how to utilize filtered portal sum value

    tomswell

      Title

      how to utilize filtered portal sum value

      Post

      PROBLEM: when viewing filtered portals ( with same filter values ) I can't seem to create a value for display and further calculation.

                                desired value    =   ( filtered sum * number from other table  )

                                                                      hours times dollars

       

      2- tables  

      TODO fields;

      payee_Key >      text- global, by valuelist (employee ids)  [filter]

       

      laborers >       text-indexed {multiple values can be selected into this field via drop down list > valuelist(employee ids)}

                                     (subject list filtered shows individual EMPLOYEE data)

      sum_time total > time- value rendered by filtered list viewed in single line portal

                                     (single line portal value SUM  representing total hours for selected EMPLOYEE)     

       

       

      EMPLOYEE fields ;

       id > number-   primary parent key

       emp_rate> number-   wage

       

      GetAsNumber ( sum_time_total ) seems appropriate first step ?

      perhaps a conditional "on object modify" statement applied to ( sum_time_total ) creating a $$global ?

       

       

       

       

       

        • 1. Re: how to utilize filtered portal sum value
          philmodjunk

          I can't quite figure out your current set up for your portal.

          Can you describe how the two tables are related?

          • 2. Re: how to utilize filtered portal sum value
            tomswell

            I havent found a table scheama that gives me access to  TODO::filtered sum_total_time   *  EMPLOYEE::emp_rate

            Attached is a grab of basic layout which I would describe a Ping-Pong arrangment > buttons allowing sort of a commit/uncommit arrangment for TODO time records.

            I have a near identical set-up for  Bank deposits <> Job Payments which has worked for years.

            I'm certain I can script buttons to get task done ... I simply cant seem to display  rate* filtered sum at bottom of filtered list.

            • 3. Re: how to utilize filtered portal sum value
              philmodjunk

              Yes, but I am not clear on the intended "work flow" of your data into this system. I see a layout, but do not know on what table it is based. I see two different portals but do not know what relationships you have currently defined to relate them to the layout's table.

              You might spell this out as though you were doing this as a paper and pencil excersize step by step so I or another poster can suggest a data model that works for you here.

              • 4. Re: how to utilize filtered portal sum value
                tomswell

                here's another shot at explaining situation  ... 

                the filtered portal is for displaying individual payee time / part of "todo::laborers" 

                todo::laborers >       text-indexed {multiple values can be selected into this field via drop down list > valuelist(employee ids)}

                                             

                todo_filter= c_isPayee_key=1 and todo_filtered::c_isTime=1

                     c_isPayee = PatternCount (laborers; paychecks_::PayeeKey  )>0  

                     c_isTime  =  Case ( TimeTotal=""; 0;1)

                • 5. Re: how to utilize filtered portal sum value
                  philmodjunk

                  In the table occurrences shown at the op of your last post, do occurrences with the same data source table have the same color?

                  From the name, I'd assume that one record on this layout represents a single paycheck?

                  What type of field is PayeeKey? an unstored calculation or a field with global storage?

                  Please explain the purpose of this field:

                  laborers >       text-indexed {multiple values can beselected into this field via drop down list > valuelist(employee ids)}

                  Does "multiple values can be selected" mean that this field stores a list of different employee ID's?

                  • 6. Re: how to utilize filtered portal sum value
                    tomswell

                    yes same color for data

                    paychecks::PayeeKey - Global, by valuelist  showing employee::id    ( drop down under "Who" button )truly the "KEY" to having pacheck window focus on individual employee / as with my similar successful deposits layout I plan to include a TAB showing all paychecks for selected employee

                    the whole point of shown layout is to generate an individual paycheck by selecting todo time records for employee selected by PayeeKey

                    which when printed will include a report listing selected records ... I imagine I'll have to create another table called PaycheckItems.

                    AND THANKS for such fast response! amazing.  have to go to work till eve.

                     

                    • 7. Re: how to utilize filtered portal sum value
                      philmodjunk

                      Note that this relationship:

                      paychecks_::PayeeKey = paychecks_Payeekey::PayeeKey

                      is the same as:

                      paychecks_::anyfield X paychecks_Payeekey::anyfield

                      Any record in paychecks_ will match to all records in paychecks_payeekey.

                      Thus, I would expect the left portal to list all records in paychecks_

                      to repeat:

                      Please explain the purpose of this field:

                      laborers >       text-indexed {multiple values can beselected into this field via drop down list > valuelist(employee ids)}

                      Does "multiple values can be selected" mean that this field stores a list of different employee ID's?

                       

                      • 8. Re: how to utilize filtered portal sum value
                        tomswell

                        I really haven't approached the left portal yet ... I just go hung up on the cosmetic of not finding a way of showing employee rate  times accumulated hours under the right portal list. 

                        The todo::laborers field is indeed intended to store one or more employee id's.

                        What I like about that PayeeKey drop down is dynamic accepting any valid payee id.

                        AND

                        it's one field as opposed to a field for each employee, in which case, it would be simple to make a direct = relation table for each employee from which rate*time .

                        Note that the filter is based on two conditions being true;

                        1. there is a job (which could be [invoice,estimate or contract]

                        2. start time and finish time are entered thus allowing calculated hours.

                        I imagine I will not allow todo records to be deleted if isHours (true) and laborers is not empty. ... I'm thinking i'll remove payeeID from todo::laborers when paycheck item is generated.

                        IE: moved from right portal to left portal.

                        start time alone is handy for indicating appointment time

                        invoice description is formal lookup where as note is data often accumulated from phone activity as a starting point

                        likewise name is a people lookup if people id is entered or beginning of new people record if invoked (typing twice avoidance)

                        • 9. Re: how to utilize filtered portal sum value
                          philmodjunk

                          First to answer the basic question as I've been getting distracted by other issues with your design:

                          A filtered portal with a summary field in it works to display a total of the related records, but other calcualtion fields, which evaluate at the data level instead of the layout level will not be affected by the portal filter and will refer to all the related records not just the subset of those related records that pass through the filter. A script if it properly set's the focus on the summary field in the filtered portal can capture the value to copy to another field or a variable. You'd need to set up a script trigger to do this each time you update the values used in your filter.

                          An alternative approach is to get rid of the filter expression and build the same criteria into the relationship. This is not always feasible, but if you can do it, then your calcultions can also work to produce the desired results.

                          Using a single field to list employee ID's is often not the best approach for linking many employees to many TODO records. Setting up a Join table to serve this function is almost always a more flexible option.

                          • 10. Re: how to utilize filtered portal sum value
                            tomswell

                            Wink... laying in bed I thought about "script triggering" on changing of value   >  sum_time total which is in the filtered one line portal.

                            I'll give it a try tonight

                            THANKS!

                            • 11. Re: how to utilize filtered portal sum value
                              tomswell

                              Humm? ... the circle comes around to origional question

                                 "I can't seem to create a value for display and further calculation."

                              tally of hours displays correctly in 24hr format.

                              " A script if it properly set's the focus on the summary field in the filtered portal can capture the value to copy to another field or a variable. You'd need to set up a script trigger to do this each time you update the values used in your filter."

                              how do I "properly set focus in a scripted trigger ?

                              "self" > is not allowed !

                              GetAsNumber(todo_filtered::sum_time total) > yields unfiltered value as predicted.

                              "This is not always feasible" seems to be the answer

                              • 12. Re: how to utilize filtered portal sum value
                                tomswell

                                I'm still hooked on current layout overall, giving up on multiplying with filtered value.

                                So I preceeded to see if I could script gathering todo values so as to make a new pacheck item record.

                                Seems close. Watching script ...  enter find mode > Paste  does what I'd expect placing find value into @todo::id.

                                but script seems to ignore Perform Find.

                                if i exit script and perform find from menu bar all is good and I get my variables.

                                 

                                I'm already embarresed cause I fully expect it's my ignorance.

                                whats wrong with this picture ?

                                 

                                in the data viewer;

                                @todo::find_record_id    is a global I set up as a test of using a field as opposed to variable.

                                 

                                • 13. Re: how to utilize filtered portal sum value
                                  philmodjunk

                                  To set the focus on the correct field use go to field and specify the summary field (Only if there are no other instances of this field on your layout) or use the "name" box at the top of the inspector's position tab to give the field (or the porrtal object) an object name. Then use Go To Object to set the focus.

                                  Go to Object ["SummaryPortal"]
                                  Set Field [CheckTotal ; //put calculation that refers to summary field total here or just copy the total into the target field]