4 Replies Latest reply on Sep 8, 2011 1:38 PM by philmodjunk

    Adding line items through asset list portal

    TimothySlinks

      Title

      Adding line items through asset list portal

      Post

      I want to view an asset list in a portal and allow the user to click a button next to the asset and add it as a line item. So the assets table would store the twenty different widgets that we sell.

      I have attached a GUI design to reference. The asset list has all the blue arrows in it.

      It looks like I need two portals, right? How can I view all of the available assets if the line items list is empty? What sort of relationship should exist between the asset table and the line items table?

      What scripting trickery needs to happen to deal with these two portals?

      Does this make sense?

      Thank you in advance for your time. I feel like an idiot; I'm afraid to look this software in the eyes.

      projectwindow-mockup.png

        • 1. Re: Adding line items through asset list portal
          mgores

          I did somthing similar with a solution for my sons scout troop (after a lot of help here).  I wanted to be able to select scouts from a portal listing all of them and have them move over to a "participated" portal. 

          First you need a join table, called EventPeopleJoin in the script below. The script below copies the scout(asset) from the first portal to the second portal.  The first portal is an X type relationship to the scout table and the second an = type based on the eventID.  In my solution I wanted the scout to be filtered out of the first portal once selected, so you may not need the refresh window [flush cached join results] step

           

          Set Variable [ $scoutID; Value:scouts list::scoutID ]

          Set Variable [ $eventID; Value:Event::EventID ]

          Freeze Window

          Go to Layout [ “EventPeopleJoin” (EventPeopleJoin) ]

          New Record/Request
          Set Field [ EventPeopleJoin::AttendeeID; $scoutID ]
          Set Field [ EventPeopleJoin::EventID; $eventID ]
          Go to Layout [ original layout ] Refresh Window
          [ Flush cached join results ]

          • 2. Re: Adding line items through asset list portal
            philmodjunk

            First you need relationships for your portals. You haven't provided a complete picture here so what I am about to post is based on some educated guessing about the structure of your database. Please substitute your names for mine:

            Invoices----<LineItems>----Products
                 |
                 ^
              AllProducts

            Where all products is an occurrence of products

            Invoices::InvoiceID = LineItems::InvoiceID
            Products::ProductID = LineItems::ProductID
            Invoices::anyfield x AllProducts::anyfield

            To add AllProducts, you'd select Products in Manage | Database | Relationships, then click the button with two plus signs to duplicate it. Then double click it to rename it. Then you can drag from any field in Invoices to any field in AllProducts to link them in a relationship. Doubleclick the relationship line and change the operator from = to x so that all records in Products will be linked to any given record in Invoices.

            Now you can add portals to LineItems and AllProducts to your layout, which should be based on Invoices.

            With this structure, your button in the portal to AllProducts can then look like this:

            Set variable [$InvoiceID ; Value: Invoices::invoiceID]
            Set variable [$ProductID ; Value: AllProducts::ProductID]
            Freeze Window
            go to layout ["LineItems" (LineItems)]
            new record/request
            Set field [LineItems::ProductID ; $ProductID]
            Set field [LineItems::InvoiceID ; $InvoiceID]
            Go To layout [original layout]

            • 3. Re: Adding line items through asset list portal
              TimothySlinks

              PhilModJunk--

              THANK YOU!

              Will this "occurance" of Products reflect any changes to the original? ie will AllProducts include a new record I create in Products?

              Even with the cartesian relationship, the AllProducts portal does not display anything until a line item exists. What am I doing wrong?

              • 4. Re: Adding line items through asset list portal
                philmodjunk

                The relationship to all products does not involve the line items table either directly or indirectly, thus I can't see any way that the presence or absence of a line item will affect it. You do need an invoice record first, but that shouldn't be a problem here as creating a new invoice should be the first step in the process anyway...

                Occurrences are simply convenient labels for a given table so that you can define multiple relationships bewteen the same tables. For more on the subject:  Tutorial: What are Table Occurrences?