7 Replies Latest reply on Mar 25, 2014 4:37 PM by JammieSchmunk

    Needing summary

    JammieSchmunk

      Title

      Needing summary

      Post

           I have a portal with

           Entity
           Loc
           St
           Code
           Payroll

           I need to summarize this by the top four fields. I need total payroll if the first 4 fields are the same, the first 3 are the same, etc. What is the best way to do this? I did it in a Report but I need it done on a portal in my main screen.

           Your help will be MOST appreciated since I've tried to figure it out of 2 days.

            

        • 1. Re: Needing summary
          philmodjunk

               Does Loc mean "location"? what kind of data do you enter into it. what kind of data are you entering in St? State? Street? or ???

               Please explain this statement in more detail:

               

                    I need total payroll if the first 4 fields are the same, the first 3 are the same, etc.

               Can you provide an example of what you want to show? What do you mean by "etc."?

               It sounds like you want to produce a summary report inside a portal and this is not possible. Since it's not possible, other methods would need to be used and a better understanding of what you are trying to produce and why would help us to explore those other methods.

                

          • 2. Re: Needing summary
            JammieSchmunk

                 You are correct. I am looking for someway to summaries data.

                 On the screen shot it's the portal on the left. The info is brought over from the worksheet tab and I would like it to just be a summary of the worksheet showing totals for Exp. Base and Standard Prem. So the first 2 lines would be added together because _fkEntityID, ST, _fkLocNo, and Code are the same. The 3rd entry would stand alone because all 4 fields are not the same. 

                 Is it possible? Like I said, I was able to create a report (however I couldn't figure out how to just display one policy summary at a time - which all of this is based off of) with the information but I need it on this tab.

                 Any help you have to offer would be GREATLY appreciated. Thanks for your time!

            • 3. Re: Needing summary
              philmodjunk
                   

                        Is it possible? Like I said, I was able to create a report (however I couldn't figure out how to just display one policy summary at a time - which all of this is based off of) with the information but I need it on this tab.

                   It should be possible to set up a summary report on a layout based on the portal's table. To limit the data to just one policy, you'd either perform a find for just those records or use Go to Related records from a layout such as the one that you show here.

                   There are also ways to do it on this layout, but they will be much more complex to set up.

                   

                        because _fkEntityID, ST, _fkLocNo, and Code are the same

                   Is this the only basis for grouping that you need? (your initial post implied otherwise.)

                   One method is to set up a large, mutli-row calculation field that uses ExecuteSQL to display rows of summarized data. With careful formatting, this can produce a read-only view of the data that is very similar to a portal: FMP 12 Tip: Summary Recaps (Portal Subtotals)

                   Another option is to create an additional table where your system creates a single record for each unique combination of these 4 values. Then a relationship between it and your portal's table could be used to compute the needed sub totals and you'd change your portal on this layout to refer to this new table in order to show the needed sub totals.

              • 4. Re: Needing summary
                JammieSchmunk

                     First, thank you for helping me with this. I am not a programer...I am learning as I go. I knew there was a way to display just one policy but didn't know exactly how to make that happen.

                     Can you explain the other table method and exactly how I'd create a single record for each unique combination of these 4 values? 

                • 5. Re: Needing summary
                  philmodjunk

                       The details depend on how you create those records in the current portal in the first place. And keep the records in this table correctly updated as you add/modify/delete records in your portal's table will be a challenge.

                       Here is one option:

                       Define the new table with fields for _fkEntityID, ST, _fkLocNo, and Code. You can add in calculation fields that use aggregate functions like Sum later.

                       Define this relationship:

                       NewTable-----<PortalTable

                       Newtable::_fkEntityID = PortalTable::_fkEntityID AND
                       Newtable::ST = PortalTable::ST AND
                       Newtable::_fkLocNo = PortalTable::_fkLocNo AND
                       Newtable::Code = PortalTable::Code

                       Enable "allow creation of records via this relationship.

                       Then you can use a script trigger on your portal to perform this one line script:

                       Set FIeld [NewTable::_fkEntityID ; PortalTable::_fkEntityId]

                       This will create a new record in NewTable with these matching values in all 4 fields, but only if such a related record does not already exist.

                       But this is only the tip of the iceberg. you also would need scripts that check for and remove records from this table if editing a value in one of the portal table records means that a record in NewTable now does not match to any records in your portal's table. The same is true if you delete a portal record.

                  • 6. Re: Needing summary
                    JammieSchmunk

                         That is most helpful.... Thank you so much!

                    • 7. Re: Needing summary
                      JammieSchmunk

                           So this is the report. See how it's list 2 different policy numbers? How do I get it to just display one at a time? I checked a page break after every 1 occurrence but that didn't work.