8 Replies Latest reply on Dec 28, 2010 12:09 PM by BERGSTEN

    Multiple Occurence Table within Portal

    BERGSTEN

      Title

      Multiple Occurence Table within Portal

      Post

      I have a schedule Table with 365 records (1 for each day). This is related to an Invoice/Event table by invoice number. This way I can put an event on a day to keep track of everything for the day.

      The problem that I am having is that the schedule or "Showday" table is also related to a trucks table that looks up the type of truck based on its ID.

      I need to be able to keep track of multiple trucks on multiple shows, so they are joined by a linking table.

      Since there is no way to have a portal for trucks within the portal that is each day on the showday (as shown), I have created two multiple occurence fields (one for the number/ID of the truck and one for the result/name of the truck).

      When I use this method, the truck lookups then appear on the portal record after this as pictured. All related tables have the "Allow related records..." button checked on the relationship table side of things.

      As a side note if anyone knows how: I'd also like to be able to add portal records without an associated Invoice Id so that we can keep track of other scheduling events that may not be invoices.

      Thanks and sorry for the long-winded explanation.

      result_copy.jpg

        • 1. Re: Multiple Occurence Table within Portal
          philmodjunk

          I see what you have under Truck, but don't see in your post what the problem is with the data shown. Is it in the wrong order? The wrong data? Shouldn't be visible in the second portal row?

          As a side note if anyone knows how: I'd also like to be able to add portal records without an associated Invoice Id so that we can keep track of other scheduling events that may not be invoices.

          Without knowing more about your tables and relationships, you might want to go ahead and generate an invoice record and invoice number for all events, but use a status field in the invoice table that identifies specific events as "non invoice" or "non-billable". (There are other approaches that may be possible, but I'd have to know more about the structure of your system before I could tell if they are even possible, let alone pracitical for your solution.)

          • 2. Re: Multiple Occurence Table within Portal
            BERGSTEN

            The problem is that when I type the truck ID into the first field in the first portal row, it populates it in the second row as well (this is pictured in the "result" half of the uploaded photo.)

            • 3. Re: Multiple Occurence Table within Portal
              philmodjunk

              I thought that was the case, but wanted to be sure.

              What is the relationship between your main table and this portal? You can post that with text in this format:

              MainTable::KeyField = PortalTable::keyField

              Also, please describe how you build your list of trucks. Is this a calculation field that uses the List function? Presumably, there's a relationship involved here that I'll need to see as well.

              • 4. Re: Multiple Occurence Table within Portal
                BERGSTEN

                Showday Relationships are:

                Invoices::InvoiceID = ShowdayLink::InvoiceID <--portal table

                ShowdayLink::ShiowdayID = Showday::ShowdayID

                Truck relationships are:

                Trucks::TruckID=TruckShowday::TruckID

                TruckShowday::ShowdayID = Showday::ShowdayID

                the trucks are all text fields with make, model, ID number and type-- no lists. Each truck is its own record (we only have 8). The truckshowday link table uses lookups from the truck table. (ID and Type are the looked up fields in the linking table)

                • 5. Re: Multiple Occurence Table within Portal
                  philmodjunk

                  I'm assuming your layout above is based in Invoices.

                  Invoices---<ShowdayLink>---ShowDay----<TruckShowday>----Trucks

                  I think I see the problem, but let's dig a little deeper to be sure.

                  1. The object in the portal that lists the trucks is a repeating text field?
                  2. In which table is it defined and how do you put the truck data from Trucks (or TruckShowDay) into the repetitions of this field?
                  3. Is your portal based on ShowdayLink or ShowDay?

                   

                  • 6. Re: Multiple Occurence Table within Portal
                    BERGSTEN

                    The layout I posted is based in the showday table.

                    Invoices---<ShowdayLink>---ShowDay----<TruckShowday>----Trucks

                    ^that is correct, though.

                    1. yes. It has 6 repeating ID fields and 6 repeating type fields that lookup the type based on the ID

                    2. its defined in the <TruckShowday> linking table.

                    3. the portal is based on <ShowdayLink>

                    • 7. Re: Multiple Occurence Table within Portal
                      philmodjunk

                      Take a close look at that relationship. If the layout is based on ShowDay and the portal is based on ShowdayLink, then there is no direct connection between the ShowdayLink portal record and the record in TruckShowDay. Any data reference to TruckShowday traces from ShowDayLink to ShowDay to TruckShowDay. Thus any record in the portal is going to match to the same related record in TruckShowDay. It's the same as though you just put this field on the ShowDay layout outside the portal.

                      Each ShowdayLink matches a specific invoice to a specific "day" record in ShowDay. The only interpretation of your layout that makes sense to me is that you want a list of the trucks needed for that specific invoice. That would require a link from invoices to TruckShowday.

                      • 8. Re: Multiple Occurence Table within Portal
                        BERGSTEN

                        awesome. I was so caught up in the relationships that it didn't occur to me to think of the Invoice table as the central point.

                        Thanks! That worked perfectly.