14 Replies Latest reply on Mar 22, 2011 4:45 PM by philmodjunk

    portal issue

    mgores

      Title

      portal issue

      Post

      I have a portal that shows related records based on customerID from another DB.  The problem is that one of the fields I want to show up from the other DB is from a child table (line_items)in that DB.  I've tried setting up the relationship several ways and either get blank fields or all the portal rows show the same value.

        • 1. Re: portal issue
          fitch

          You can't have a portal within a portal, so what are you wanting to see when the child (really, grandchild) table has multiple records related to the "other DB" (which is what I'd call the "child" since it's what the portal is based on)?

          One option is to create a calculated field in "other DB":

          Substitute( List( line_items::field ) ; ¶ ; ", " )

          ... and display that field in your portal.

          • 2. Re: portal issue
            philmodjunk

            Just to describe an alternate approach you can choose between, you can also use two portals where you use scripting to enable the user to click a portal row in the "master" portal to then display the related records from the child table in the second "detail" portal.

            • 3. Re: portal issue
              mgores

              It would probably make more sense to explain the data bases a little.  The first one is a tracking solution for jobs we are working on.  The one I am showing in the portal is our quote DB.  What I wanted to show was - when a customerID is entered into a new record in the tracking DB, the portal would show the quotes sent to that customer with quote#, contact name, qty and part type.  I can then click on the right quote and have the quoteID copied to the record in the tracker job record to the quote. 

              The qty and part type used to be repeating fields and everything worked until I ran into other issues, so I added a line item table.

              I have not gotten around to deleting the original qty and part type fields, so I guess it would be easy enough to redefine them as caculations like you described and just use them for show in the portal.

              Was hoping there might be an easier way.  Smile

              • 4. Re: portal issue
                philmodjunk

                Set Field [JobTracker::QuoteID ; Quotes::QuoteID]

                is all you need to make that selection. Just put a button inside the portal row and set it to perform this script step.

                (I am assuming that the layout refers to JobTracker and the portal refers to Quotes. Modify this to match the actual names in your file.)

                • 5. Re: portal issue
                  mgores

                  That's exactly how I did that part.  The issue was determining which quote from the portal.  The portal shows the quoteID and contact name fine, but not the part type and qty since those are from the lineitem table.

                  So I made a part type field in the quote table, set it as calculated result List(lineitems::parttype)

                  but that doesn't seem to work, the new part type field is blank and when I try to Relookup on the part type from line items I get the "there are no fields that lookup values based on this field".

                  • 6. Re: portal issue
                    philmodjunk

                    These are your relationships?

                    JobTracker::CustomerID = Quotes::CustomerID
                    Quotes::QuoteID = LineItems::QuoteID

                    And selecting a quote record in the portal is to do what for the user? Start a new quote with the same items perhaps?

                    • 7. Re: portal issue
                      mgores

                      No, actually for this issue we are going the other way.  The user is entering a job into the tracker.  Once the customerID is entered, the portal shows all the quotes made to that customerID.  The user then selects the quote that matches the parts recieved from one of the portal rows.  That sets Tracker::quoteID to Quotes::quoteID to link the two records.

                      The reason for this is that for QC purposes, our jobs need to be traceble to the quotes and there has to be records that the quote (and other documentation) had been reviewed before any work is done.  The linking of the job record to the quote record allows me to show auditors all of the pertinent information from the one central quote DB without having to go into all of the individual department tracking DBs.

                      • 8. Re: portal issue
                        philmodjunk

                        Once the customerID is entered, the portal shows all the quotes made to that customerID.

                        That requires this relationship for your portal:  JobTracker::CustomerID = Quotes::CustomerID

                        Otherwise, you cannot list the quote records for your suggested portal. You may have an additional relationship to a different table occurrence of Quotes where you can link by selected QuoteID, but you need the first in order to display the list of quotes in a portal.

                        If you don't have the second relationship somewhere in your database, I don't see how you have a working quote system with multiple items listed for each quote.

                        Let's try a variation of Fitch's suggestion. Define a calculation field, cPartQty in LineItems as: PartID & "  " & Qty. Now select a field or group of fields in your layout and define a tool tip expression for it as List ( LineItems::cPartQty ). This will pop up a list of the parts and their quantities inside the tool tip when you hover the mouse over the objects in the portal row. This assumes that the portal is to a table occurrence that is related to a Table Occurrence named LineItems. Then clicking a button in the Quotes portal row does the step I posted earlier. You can, if you choose, add this relationship:

                        JobTracker::QuoteID = JobTrackerLineItems::QuoteID

                        JobTrackerLineItems would be a new table occurrence of LineItems. A portal to it would display the quote's line item records when JobTracker::QuoteID is assigned the matching QuoteID.

                        • 9. Re: portal issue
                          mgores

                          Phil, There is a relationship for customerID between the two for that portal (as well as one for quoteID used to link job(s) to a particular quote).  Within quotes there is the lineitems child table that is related to the main table by quoteID.  The problem is that the JobTracker::quoteID is going to be set by clicking on the portal row, so the JobTracker to Lineitems relationship won't work.

                          I guess I really am trying to show a portal within a portal.

                          • 10. Re: portal issue
                            philmodjunk

                            Take another look at that last suggested portal from JobTracker to line items. It's not intended to display anything until after the user clicks on a portal row button to select a quote for the current jobTracker record. Thus, it helps the user confirm their choice rather than help them make the choice in the first place. That's where the tool tip came in and it is indeed intended to serve as a crude "portal within a portal" substitute.

                            • 11. Re: portal issue
                              mgores

                              OK, I get that part now.

                              The tool tip always shows the latest qty_parttype, i.e. does not change for each portal row.

                              n.m.  figured out the relationship issue there

                              The tool tip thing will work for this application I think.  Though if it will show in the tool tip I should be able to have that as a field in the row also, shouldn't I?

                              • 12. Re: portal issue
                                philmodjunk

                                Yes, you could define a field in the portal row. It's just a matter of whether you have space in the portal row to display that data. The tool tip is a "cute trick" that doesn't require space within the portal row and grows or shrinks to suit the number of related line items.

                                Subsitute ( List ( LineItems::cPartQty ) ; ¶ ; ", " )

                                will produce a list that is horizontal, with commas separating each list item instead of carriage returns.

                                • 13. Re: portal issue
                                  mgores

                                  Yes, that works.  The line items are not that long, usually something like "5 transistors" so the space is not really an issue.  Just enough info for the user to see which portal row matches with the job being entered.

                                  • 14. Re: portal issue
                                    philmodjunk

                                    Two calculation fields, one listing the Qty and one listing the parts will probably look nicer than the combined version I suggested for the tool tip.