13 Replies Latest reply on Mar 14, 2014 9:47 AM by philmodjunk

    Usinbg "sub-summary when sorted by" in portals, or other suggestions

    JoeBuratti

      Title

      Usinbg "sub-summary when sorted by" in portals, or other suggestions

      Post

           I have a solution taken straight from the sample Estimates solution, with slight modifications.  What I need to create is a side-by-side layout of all estimates created with a list of all estimates with an "inquiry" status on one side and all with "Hired" status on the other. 

           I figured creating a new layout and placing 2 portals side-by-side in it with one showing hires and the other showing inquiries would accomplish this, however I am having trouble formatting this.  What I ultimately want is to be able to group each portals list by month. 

           To back up a minute, I have already created this list with proper grouping using "sub-summary when sorted by" and it displays my master list of estimates first grouped by inquiry or hire and then grouped by month, with totals for each afterward.  This is exactly what I need, however this list is one master list top to bottom.  If I want to compare inquiries to hires for a particular month I have to keep scrolling up and down to compare.  What I need is this exact list and layout (meaning monthly headers and totals) but have all inquiries on one side/list/portal and hires on the other.

           Like I said I have created a layout that can display the hires in one portal and inquiries in the other, but I cant figure out how to group and subtotal by month in each portal.  Also, in my master top to bottom list I already created I added a date range search to the header.  I would also like this to be included in my side-by-side layout.  How would I incorporate this "filter" when dealing with portals.

           An example of the layout I am looking for is as follows:

           Portal1:                                                                  Portal2:

      Inquiries                                                                 Hires

      January                                                                  January

                 Estimate1                                                              Estimate2

                 Estimate3                                                              Estimate4

           January Total   $xxx.xx                                          January Total   $xx.xx

      February                                                                 February

               Estimate5                                                                Estimate6

               Estimate7      

           February Total   $xx.xx                                         February Total   $xx.xx

           Total Inquiries $xx.xx                                            Total Hires   $xx.xx

            

           Any suggestion on how to accomplish this?

        • 1. Re: Usinbg "sub-summary when sorted by" in portals, or other suggestions
          philmodjunk

               SubSummary layout parts cannot be put inside a portal. Since you already have the two summary reports, you might open two windows side by side to get a side by side comparison.

          • 2. Re: Usinbg "sub-summary when sorted by" in portals, or other suggestions
            JoeBuratti

                 Thought about that already and would like to NOT go that route unless its the only possible solution.

            • 3. Re: Usinbg "sub-summary when sorted by" in portals, or other suggestions
              philmodjunk

                   It may be the only way to get the summary report format you show in your original post. If you are ok with other ways to arrange the data on the screen, there are a number of alternative methods available, but you won't get the Month Name based sub headings shown in your current example.

              • 4. Re: Usinbg "sub-summary when sorted by" in portals, or other suggestions
                JoeBuratti

                     I am completely fine with exploring other methods to accomplish at least a similar design to what I am looking for.  I understand that sub-summaries do not work in portals, but I was just using that as an example of how I want the layout to look.  Any other tricks or methods to accomplish something similar would be just fine, but I need direction as to what I would need to do for those...

                • 5. Re: Usinbg "sub-summary when sorted by" in portals, or other suggestions
                  philmodjunk

                       I understand that, but what I was about to suggest would not match how you want the layout to look because it would not have had sub summary style subheadings.

                       But before discuss an approach that puts the data in a two column format, are you aware that a single summary report could be produced that looks like this?

                  January                                                                

                             Inquiries
                                 Estimate1
                                 Estimate3
                         January  InquiryTotal   $xxx.xx                                                             

                               Hires
                                    Estimate2
                                    Estimate4
                          January Hires Total   $xx.xx

                  February                                                                 
                             And so forth...

                       Total Inquiries $xx.xx                                           
                       Total Hires   $xx.xx

                       I mention that option as it's a logical extension of your current summary report design.

                  • 6. Re: Usinbg "sub-summary when sorted by" in portals, or other suggestions
                    JoeBuratti

                         Yes I actually already have that exact layout created before I attempted this 2 column layout.  Well, almost exactly.  My layout is grouped first by hire status and THEN by month, however your grouping of month first then status does make browsing and comparing much easier on the eyes. 

                         For sake of argument and in an attempt to learn something, what would you suggest I do to have the 2 column format, even if it cannot include the subheadings?  I mean beyond the basics of just having 2 basic portals side by side, which is what I already have.  Would you suggest anything different or in addition?

                    • 7. Re: Usinbg "sub-summary when sorted by" in portals, or other suggestions
                      philmodjunk

                           There are multiple options. Since the above is simpler to set up, I wanted to be sure that you had considered it first.

                           Since my original post, I've actually been thinking about whether or not a two column format with sub heading might be possible after all...

                           What I'm thinking of is to define a new table where you create one record for each month of the year. A date field in this table can auto enter something like this: Get ( CurrentDate ) - Day ( Get ( CurrentDate ) ) + 1 so that it enters the date for the first day of the month. A similar calculation can produce the same value in your table of Estimates.

                           The relationship for these portals would match by these date fields.

                           In a layout based on that table, you could put two filtered portals, one for Hires, one for Inquiries side by side under the date field formatted just to show the month name or the month name and year to get the sub heading.

                           To get the monthly totals, one row filtered portals could show a summary field defined in your table of estimates to show the total hires and total inquiries.

                           Your grand total fields would be a bit trickier to set up, either using ExecuteSQL or additional one row filtered portals to produce the needed grand total Hires and grand total inquiries.

                      • 8. Re: Usinbg "sub-summary when sorted by" in portals, or other suggestions
                        JoeBuratti

                             Sorry of the delay in responding.  I was away for a few days.  That is a very interesting way of thing of how to get it to work.  I have not yet tried it out yet but will try sometime later today.  I just wanted to touch base with you to thank you for the suggestion.  I think I understand what you are saying, but le me just rephrase it in my won working to see if I fully understand (also slightly modified):

                             Create a new table with the fields: MonthName, MonthNumber, and enter the 12 months in it with corresponding month number (Jan =1, Feb =2, etc.)

                             Add a field to my estimates table which calculates the month number based on the inquiry date, and define a relationship to the MonthNumber field in the new table above

                             Create a new layout based on the new table entering the 2 filtered portals, then in theory for each of the 12 records in the new table it will display only that months records in the corresponding portals.  I understand filtering years would require additional steps, but is this basically what you are suggesting?  I didn't fully understand how or why you would need to calculate the first of the month in the new table in your suggestion, but would my suggestion of using month numbers instead return the same results? 

                             I think that's at least a starting point I can work with. I may need additional help when it comes to the totals though and filtering the years.

                             I will attempt creating it a little later today.

                        • 9. Re: Usinbg "sub-summary when sorted by" in portals, or other suggestions
                          philmodjunk
                               

                                    Create a new table with the fields: MonthName, MonthNumber, and enter the 12 months in it with corresponding month number (Jan =1, Feb =2, etc.)

                               Simpler than that. All you need is a single date field with the date for first day of that month. This one field has all you need for matching records by month and year. A script can loop to initialize this table with the needed records and dates and the auto-enter calc could be used to add a new record with each new month if you want to use it.

                               

                                    Add a field to my estimates table which calculates the month number

                               No. Add a field that computes the date for the first day of the month from the date field you already have in this field. This is simpler than managing one field for the month and another for the year.

                          • 10. Re: Usinbg "sub-summary when sorted by" in portals, or other suggestions
                            JoeBuratti

                                 Ok I kind of understand you...  BTW, I did get it about 98% working using my month number suggestion and a date range search in the header, but now I have other problems related to a different change of this had to make.  I will attempt to change it to your suggestion but that will not correct the other problems I am having and may need additional assistance with that.

                                 Originally I inserted 2 copies of the estimates table as portals and filtered each by hired/inquiry status.  This worked but then I noticed that if the hire date was different than the inquiry date, that record would be listed under the inquiry month not the hire month.  This is understandable because the relationship is on the inquiry month field.  To correct this I then created 2 more instances of the estimates table naming one Hired estimates and the other Inquiry estimates.  These instances are not too different from the Pending and Accepted Estimates table instances in the Estimates sample database.  I created appropriate relationships for each of these instance tables (Inquirymonth->Month and Hiremonth -> Month) and replaced the 2 "filtered" portals with one each of these new instances. 

                                 This yielded the correct display of records for the correct months in the correct columns.  My problem though is that now I am getting <unrelated table> in some fields of the portal records.  Originally these fields were taken from the related customers table (customer name and email address), but I guess since these are now new "instances" of the estimates table it is not seeing that original relationship, hence showing that error.  Obviously a simple solution would be to just re-relate these new instances back to the customers table by the customerID field, however it will allow me to only relate one table or the other, not both.  Once one is created and I try to create the other relationship I get the error: "There cannot be more than one relational path between any two tables in the graph..."  This puzzles me because those relationships are allowed in the sample estimates database which is what my database is based off of.

                                 Any suggestions on how to correct this new problem?  And also, as of writing this I still used the month number method, but plan on trying out your suggestion.

                            • 11. Re: Usinbg "sub-summary when sorted by" in portals, or other suggestions
                              philmodjunk

                                   There's a fundamental difference in what I understood and what you actually have. I did not understand that the same record can be both a "hire" record and an "inquiry" record. I assumed that each record was one or the other, but not both.

                              • 12. Re: Usinbg "sub-summary when sorted by" in portals, or other suggestions
                                JoeBuratti

                                     No, you were originally correct.  They cannot be both Hired and inquiry at the same time.  They are stored in the same table, but that table has a status field that determines what status they are (Hired or Inquiry).  That table also has a inquiry date field and a Hire date field.  The Inquiry Date field is auto populated upon the entry of a new record whereas the Hire Date field is populated whenever the status is changed from Inquiry to Hired.  After the status is changed to Hired, the Inquiry date still holds its original data but it is irrelevant at this time (at least for the layout I am having trouble with)

                                • 13. Re: Usinbg "sub-summary when sorted by" in portals, or other suggestions
                                  philmodjunk

                                       OK, but I did assume that you had a single date field to use here.

                                       

                                            My problem though is that now I am getting <unrelated table> in some fields of the portal records.

                                       One solution is to add in more occurrences that link to your newly added occurrences so that you can add the needed related fields from them.

                                       Another is to define a calculation field such as this:

                                       If ( IsEmpty ( HireDate ) ; InquiryDate ; HireDate )

                                       And use that as your match field. Then inquiries will match by inquiry date but Hires will match by hire date all in one relationship.