14 Replies Latest reply on Aug 5, 2009 1:46 PM by etripoli

    Portal View of multiple records

    timothy2k

      Title

      Portal View of multiple records

      Post

      I did some reading before posting, but not sure exactly how my setup can use a self-join (if I even need to) to summarize all related records in one portal.

       

      I have 5 main tables involved:

      Customer

      Vendor

      Estimate

      Quote Item

      Quote

       

      A customer requests an Estimate, which has a "parent" id, and can then have multiple parts. each of those parts relates to one quote item. Quote items are combined onto one quote that contains each related quote item. Quote with some or all of the quote item parts can then be sent to multiple vendors for bidding.

       

      What I am trying to do, is have a "control panel" type layout where I can type in an Estimate "parent" id, and it will then display all the related quotes aong with quote items, sorted by vendor.

       

      Here is some numbering examples to make it clearer:

       

      Estimate 10020

      Parent ID 10020

      Part 1 - 10020-1

      Part 2- 10020-2

       

      Quote Items (where the letter code is specific to the vendor it was created for):

      10020mvp

      10020-1mvp

      10020-2mvp

      10020jac

      10020-2jac

       

      Quotes: (notice not every quote item ends up being on every quote)

      10020mvp

      contains quote items- 10020mvp, 10020-1mvp, and 10020-2mvp

       

      10020jac

      contains quote items- 10020jac, 10020-2jac

       

      So I am trying to get my portal to allow a search for Estimate parent ID "10020" and return all records (in rows) summarized like quotes are above, but on one screen.

       

      Right now the portal is pulling up two different records (one for 10020mvp, and one for 10020jac). 

       

      So, the tables are related right now like:

      Estimate::estimate_id--> QuoteItem::estimate_id

      QuoteItem::quote_id-->Quote::quote_id

       

      quote_id is basically the estimate_id + vendor code, which makes it unique per quote. I am looking to get all quote items with the same parent id for every vendor, displayed into one portal.

       

       

      Hope this all make sense. Thanks!

       

       

       

       

       

       

       

        • 1. Re: Portal View of multiple records
          etripoli
            

          It would seem that if you setup a calculation field, that extracts the estimate/quote number, you could setup a second relationship to show records related by that number.  If the quote number is always 5 digits long (which may be a bad assumption), you could simply take the 5 left characters.  Otherwise, it might be safer to use a custom function that returns the numbers, until it reaches a non-digit.  Ex:

           

          Function Name: Group

          Function Parameters: String

          Function:

          If (
          Not IsEmpty ( GetAsNumber ( Left ( String; 1 ) ) );
          Left ( String; 1 ) & Group ( Right ( String; Length ( String ) - 1 ) )
          )

          • 2. Re: Portal View of multiple records
            timothy2k
              

            Hmm, so I did already have a calculation field that stored the quote id.  I created a self join for the QuoteItem table, based on "estimate_parent_id" and called that table "QuoteSummary"

             

            Then my layout is attached to the QuoteSummary table, and I am trying to use my portal to display related records from QuoteItem.

             

            This has gotten me further, but there is still something wrong. It is showing only the first vendor, even though it shows all quote items (for all vendors) below that. And it is still creating more than one record, even though the information is the same on each record.

             

            Any further suggestions? I'm sure I have a relationship messed up somewheres......

             

             

            • 3. Re: Portal View of multiple records
              etripoli
                 I would think that you would want the layout based on the Estimates table, and relate the other tables to it by estimate_parent_id.
              • 4. Re: Portal View of multiple records
                timothy2k
                  

                Well, relating the other tables to estimate by estimate_parent_id doesn't allow me to relate all the separate parts of the estimate to each quote item, per vendor.

                 

                I inserted a pic of the current relationship setup, maybe this will make whatever I have wrong easier to spot.

                 

                -------

                With this setup, if I create 1 estimate (10020), with 2 extra parts (10020-1, 10020-2)- then create a quote out of those for two different vendors (10020ans, 10020hans)- the layout with the portal ends up with 3 records, each with 6 rows. They are all the same except for the estimate_id- which show (in this order) : 10020, 10020-1, and 10020-2 respectively.

                 

                *The layout with the portal is attached to the Estimate table, and the portal is trying to get related records from the QuoteItemSummary table.

                 

                 

                 

                Relationships

                 

                • 5. Re: Portal View of multiple records
                  etripoli
                     Make new instances (copies) of the child tables, and relate those with the Estimates table, use them for the portals.
                  • 6. Re: Portal View of multiple records
                    timothy2k
                      

                    I'm not sure I completely understand this, sorry.

                     

                    Your suggestion is to make a copy of QuoteItem and Quote tables, then relate both those directly to the Estimate table via estimate_parent_id, and create the portals using those copied tables?

                     

                    Also, I'm guessing this would include updating any scripts that populated records in the original tables to also write records to these copied tables every time as well?

                     

                    If you look below, I added three tables (on the left) ...does this look like what you were expecting?

                     

                    • 7. Re: Portal View of multiple records
                      etripoli
                        

                      Looking at your relationships closer, I'm not sure I understand the way you have the tables related.  In your first post, you mention that each Estimate can have multiple parts, and each quote corresponds to the Estimate ID & Vendor ID, and then the Quote Items relate to the Quotes.  But, in your graphic, you have the Estimates table directly related to the Quote Items table.  I was trying to provide a shortcut with my previous advice, relating quote items directly to Estimates, but you did that already.

                       

                      Such a shortcut would not allow any data entry, as there is not enough information to correctly create records in Quote Items.  But, before I make any other suggestions, in which table are you storing the Estimate parts (-1, -2, etc)?

                      • 8. Re: Portal View of multiple records
                        timothy2k
                          

                        Well, I realized I could have also has an "Estimate Items" table, and maybe I will end up needing one- but it didnt seem necessary for what I was doing.

                         

                        The flow goes like this:

                        Create new Estimate (lets say 10020), if there are no other parts to it, you can enter a vendor code and click "Create Quote". That  button will create a matching Quote item (10020) for that vendor AND, (if a quote does not already exist for that Quote ID and requested Vendor combo), it will also create a record in quote (10020 + vendor code). 

                         

                        Now, if I add another item to that Estimate- say 10020-1, choose the same vendor and click create quote- it will add a new quote item (10020-1)  for that vendor, and since it will find a parent quote (10020) already exists for that vendor in quote table, it will just append that quote item to the quote (quote 10020 + vendor code will now contain both quote item parts on one quote).

                         

                        This pattern can be repeated for mutiple vendors. It all "seems" to be working ok. The trouble came when I wanted a control panel to show these estimates along with there associated parts, grouped by quotes with the vendors.

                         

                        The idea is to type in a search for parent Estimate 10020, then the portal should return a list that includes all associated records where a quoted bid price can be entered. the winnind bid then gets a purchase order created which displays info about those quote items along with a few other things:

                         

                        Estimate 10020 (includes parts 10020-1, 10020-2)

                               Vendor- JAC Inc.

                               Quote 10020JAC              bid price $75.00               [create PO for this quote]

                               Quote 10020-1JAC          bid price $80.00

                         

                               Vendor- Hans Inc.

                               Quote 10020Hans             bid price $83.00              [create PO for this quote]     

                               Quote 10020-1Hans          bid price $93.00

                               Quote 10020-2Hans          bid price $97.00

                         

                         *Just to clarify also- the Estimate table has 1 record for every estimate part (a parent is considered 1 estimate part itself).  Quote item has 1 record for each estimate part AND vendor code, so there can be mutiple records in that table for the same estimate part. Quote is then unique by parent_estimate_id and vendor code, so there should never be more records in Quote than in Quote items.  Hope that makes sense.

                         

                         

                         

                         

                         

                         

                         

                        • 9. Re: Portal View of multiple records
                          timothy2k
                             Was there any other information I should provide to help me get closer to a solution for this?
                          • 10. Re: Portal View of multiple records
                            etripoli
                               Definitely sounds like Estimate needs to be related to Quote.  The problem you will have with a single layout used for all data entry for a single quote, is properly identifying how the records you create in a portal relate to the Estimate, Quote, and Vendor.  If you want that functionality, you'll need to include fields in the portals to select the proper 'key' values.
                            • 11. Re: Portal View of multiple records
                              timothy2k
                                 I'll try to play around with relating Estimate (I'm asumming you mean directly?) to Quote. But it seems like QuoteItem also needs to be directly related to Estimate to pull in all the specs......Quote was just a combination of QuoteItems.
                              • 12. Re: Portal View of multiple records
                                etripoli
                                  

                                Since QuoteItems is related to Quote, and in turn, Quote is related to Estimates, you shouldn't have to use a direct relationship from QuoteItems to Estimate.  Think of it this way - what is the first thing you create in the process?  And the second, third, and fourth?  If you start with an estimate, then create a quote, and then specify quote items, that's the order you want your relationships.

                                • 13. Re: Portal View of multiple records
                                  timothy2k
                                    

                                  Right, I actually create an Estimate, then a Quote Item, then a Quote by combining Quote Items....

                                   

                                  So that is my original layout, and leaves me in the same situation as when I started.

                                   

                                  I honestly have almost all of this working, maybe I need another table somewhere that can do some kind of in-between relationship for pricing and a report type view.

                                   

                                  I'll have to keep digging.

                                  • 14. Re: Portal View of multiple records
                                    etripoli
                                      

                                    Again, for a view only layout, you need:

                                     

                                    A layout based on Estimates

                                    A new relationship, between Estimates & Quote Items, based on estimate parent ID

                                    A portal based on that related Quote Items table

                                     

                                    If you make the estimate_parent_id field in Quote Items a number field, and not a calculation, you will have the ability to 'Allow creation of records' in the portal, but you'll need to include the fields for specifying the exact Estimate ID and Vendor in the portal.