13 Replies Latest reply on Mar 13, 2012 1:38 PM by mgxdigital

    Combining Fields for Portal

    mgxdigital

      Title

      Combining Fields for Portal

      Post

      Hello,

      I am trying to make portal that shows all of my stocks with their total sales.

      Right now, my portal will show item A sales $30, item A sales $10, item A sales $5....but i what i would like for it to show is one portal line for item A with a total sales of $45, then item B will be on the next portal line.

       

      Is there a way for me to limit the portal to only one line per item and total the sales?

        • 1. Re: Combining Fields for Portal
          philmodjunk

          Summary report with sub summary parts can do this but not rows in a portal.

          You can set up a related "summary" table with one record for item A, one Record for Item B and use a relationship to your table of items sold to compute summary totals. You might even be able to use your Products table for this purpose. To set up the relationship for your portal, consider using the X operator to match to all summary or Products records, then use a portal filter, if needed to filter out records you don't want listed in the portal (such as items with 0 total sales).

          • 2. Re: Combining Fields for Portal
            mgxdigital

            Thanks Phil.

            I can get a related table showing all records, but can not get the "summary" table like you mentioned showing only one record for item 1, one record for item B,  etc. How can i get this?

            • 3. Re: Combining Fields for Portal
              philmodjunk

              You'd need a relationship such as:

              ReportLayout'sTable---<Products>---SalesItems

              ReportLayout'sTable::anyfield X Products::anyfield

              Products::ProductID = SalesItems::ProductID

              This is the most basic relationship, but you  may find you need to define global fields in Products to match to fields in SalesItems in order to limit the records being totaled up. (All sales items for a given date or range of dates, for example.)

              • 4. Re: Combining Fields for Portal
                mgxdigital

                In regards to the global field:

                 

                I am putting the products portal on a customers tabel based layout.

                So each customer has it's own portal of items ordered.

                I only have one table for products. So i'm relating customers to products but then i don't have another salesitem table to join it to.

                Shoudl i just do a self join of products to itself? If so, do you recommend any type of global field?

                • 5. Re: Combining Fields for Portal
                  philmodjunk

                  You can select a "box" in Manage | Database | Relationships and then click the duplicate button (two green plus signs). This creates a duplicate "occurrence" of an existing table. It's not a new table, just a new way to refer to an existing one. You can use such added table occurrences to make additional relationships between your tables.

                  Given your relationships you might set up this arrangement:

                  Customers---~invoices table?~----<itemsOrdered>---ProductsSummary---<ItemsOrderedByProductID

                  I'm guessing you may have an invoices table in there, but it's not strictly ncessary that you do.

                  ProductsSummary would be a new occurrence of your products table. ItemsOrderedByProductId would be an added occurrence of ItemsOrdered.

                  Your portal would be to ProductsSummary and the intervening occurrence of ItemsOrdered will "filter" the related records down to just those ordered by the current client. No X operator needed in this example. The match fields in both links to ProductsSummary would be your product ID field and your calculation field would use the sum function to sum a field in ItemsOrderedByProductID.

                  • 6. Re: Combining Fields for Portal
                    mgxdigital

                    Hello Phil,

                    I'm still unable to get the portal to group the items together. I've created a mock up of the database and i'm hoping from this you might be able to help me further. *Fingers Crossed* How can i send it to you?

                     

                    I have the filter on the portal and everything just as you've explained. I'm hoping that i'm just missing something simple.

                     

                    Thank you so much for your help!

                    • 7. Re: Combining Fields for Portal
                      philmodjunk

                      The portal does not group anything. It's a portal toa different table where you have one record for every group. Since you have one record for every group, this portal can than display the totals you want. Have you created such a table. Have you created the needed records in it?

                      • 8. Re: Combining Fields for Portal
                        mgxdigital

                        Yes,

                         

                        My relationships look like this:

                        Customers - Order Tickets - Products - New Self Join Products per your above post

                        Customers:Customer Name - Orders: For Customer ; Orders: Product ID - Products: Product ID ; Products: Product ID - Products Self Join: Product ID

                        I want a portal on a customers layout to list teh products they have ordered with the totals of each products only. Right now, i can get a portal to list every item, but not group them into the summary field that i have.

                        Portal on Customers Layout is pulling from Products (Filtered via Products Self Join)

                        • 9. Re: Combining Fields for Portal
                          philmodjunk

                          To which table occurrence is the portal?

                          • 10. Re: Combining Fields for Portal
                            mgxdigital

                            The portal is the products table occurance with the filter portal records checkbox marked; filtered by products self join.

                             

                            • 11. Re: Combining Fields for Portal
                              mgxdigital

                              I did make a mock up of the database if you would like it.

                              • 12. Re: Combining Fields for Portal
                                philmodjunk

                                Apologies for my first post today. I was recalling a different thread on the same subject and didn't review previous posts carefully enough. A portal to products should list one row for every product purchased by that customer and leave out any that have never been purchased by that customer. Do you see that in the portal?

                                I think I see the problem and owe you a second apology for not forseeing this complication. You are getting totals for all sales for each product, not the sales for just the current customer, correct?

                                We'll need a relationship that filters out records from other customers. Unfortunately, this requires a set up and scripted support I would prefer to avoid, but it does work.

                                Make your relationship between Products and items sold like this:

                                Customers---~invoices table?~----<itemsOrdered>---ProductsSummary---<ItemsOrderedByProductID

                                ProductsSummary::ProductID = ItemsOrderedByProductID::ProductID AND
                                ProductsSummary::gInvoiceIDList = ItemsOrderedByProductID::InvoiceID

                                gInvoiceIDList is a global text field and a script would be required to update it's value each time you access a different Customer record:

                                Set Field [Products::gInvoiceIDList ; List (Invoices::InvoiceID) ]
                                commit Record

                                I know you have offered to share the file. You can do so by uploading a copy to a file sharing site and then posting the download link here. You can save a clone of the file so that there is no sensitive data present in the copy you upload. I've been attempting to make sure this was really necessary first.

                                • 13. Re: Combining Fields for Portal
                                  mgxdigital

                                  FINALLY GOT IT!

                                  Thanks SOO much Phil!