9 Replies Latest reply on Oct 21, 2015 8:28 AM by jdevans

    Conditional Value List using pop-up menu in portal

    jdevans

      I have the need of creating a CVL from a field in a portal. What I have to work with so far:

       

      1) Accounts table which includes account_id_pk, and a field to specify whether or not this account is "Active" (aka useable) active_status and holds value of either Yes or No.

       

      2) Travel Requests table which includes trav_req_id_pk, and other detail fields.

       

      3) TravReq_Accts_line_items JOIN table which includes:

      fk_account_id, fk_trav_request_id, active_accounts_constant_yes. In this table, TravReq_Accts_line_items::fk_account_id is related to = Accounts::account_id_pk, and TravReq_Accts_line_items::fk_trav_request_id is related to = Travel Requests::trav_req_id_pk. And lastly, active_accounts_constant_yes holds an auto-generated value of Yes for all records created. This table is to hold all the Account line items that belong to a given Travel Request record.

       

      4) I created a second table occurrence of Accounts called Accounts_Active. I set its relationship to TravReq_Accts_line_items such that Accounts_Active::account_status = TravReq_Accts_line_items::active_accounts_constant_yes


      The Layout Im working on is based on the Travel_Requests (2) table. On it, I have a portal set up so that on a Travel Request record, one or many Accounts can be entered. The portal is based on TravReq_Accts_line_items (3). The field placed on it that is to be used as a pop-up menu is fk_account_id. The active_status field from Accounts isn't on this layout, and shouldn't be. I only want the user to see active_status=Yes accounts show up in that pop-up menu. I haven't found any way of doing this successfully. Every example I've seen shows a series of dropdown/pop-up menu fields such that the second set of choices is based on the first choice, and so forth.


      My use case is that the field that should determine what my drop-down/pop-up displays is on another layout. They are related though.


      In my search for an answer, I've had the pop-up respond by showing ALL accounts, even if they were statused as active_status=No, or "<no values defined>", or by showing a single value, which I couldn't determine why exactly it was the one showing.

        • 1. Re: Conditional Value List using pop-up menu in portal
          Extensitech

          In this case:

           

          Travel_Requests (2) -< TravReq_Accts_line_items

          Travel_Requests (2) -< Accounts_Active


          Where your layout is based on Travel_Requests (2), your portal is showing TravReq_Accts_line_items, and you want a value list on those line items, where the user can select based on data from Accounts_Active....

           

          Create a value list using values from Accounts_Active, showing only related values from Travel_Requests (2).

           

          Does that help? Is there some part of that that's not working for you?

           

          Chris Cain

          Extensitech

          1 of 1 people found this helpful
          • 2. Re: Conditional Value List using pop-up menu in portal
            jdevans

            I have two relationships set up:

            Travel_Requests--< TravReq_Accts_line_items>--Accounts

            the second relationship just has a second relationship line from TravReq_Accts_line_items>--Accounts_Active


            I have tried several iterations of "Include only related values starting from:" to no avail. I either get the entire unfiltered list of Accounts, or nothing at all, or some very few seemingly random Accounts.

             

             


            • 3. Re: Conditional Value List using pop-up menu in portal
              Extensitech

              Could you please post the results of: RelationInfo ( get ( filename ) ; "TravReq_Accts_line_items" ) and maybe a screen shot of the value list definition, or maybe even a screenshot of the portal in layout mode?

               

              As described, this seems fine, and a lot like the pattern I use all the time successfully. I've got to be missing something.

               

              Chris Cain

              Extensitech

              1 of 1 people found this helpful
              • 4. Re: Conditional Value List using pop-up menu in portal

                Source:New_Database_dev2a
                Table:Travel_Requests
                Options:
                Travel_Requests::pk_travel_request_id = TravReq_Acct_line_items::fk_travel_request_id

                Source:New_Database_dev2a
                Table:Accounts_Travel
                Options:
                Accounts_Travel::_pk_account_id = TravReq_Acct_line_items::fk_account_id

                Source:New_Database_dev2a
                Table:Accounts_Travel_Active
                Options:
                Accounts_Travel_Active::active = TravReq_Acct_line_items::active_accounts_constant

                 

                • 5. Re: Conditional Value List using pop-up menu in portal
                  jdevans

                  Some of the field/table names may differ slightly from my original description, but connectivity wise, it is as originally described.

                  • 6. Re: Conditional Value List using pop-up menu in portal
                    jdevans

                    After looking at my screen-shot, I found it (the problem).

                     

                    On the setup for the Value List, the second field was from the wrong table occurrence. Once I set it to the same TO as the first (pk field), it works perfectly.

                     

                     

                     

                    Thanks!!!

                    • 7. Re: Conditional Value List using pop-up menu in portal
                      jdevans

                      Well, I thought it was fixed, but it still has issues. Seems the filtered pop-up list on shows that it has any values if there were ALREADY some account listed in the portal. For example, before I got the filtered list working at all, I made some dummy records using the full un-filtered pop-up menu list of 50-something accounts, and left them in the portal. Once I went back and deleted all the rows from the portal, and started over with a fresh record, the pop-up menu reads:

                       

                      <no values defined>

                       

                      Not sure what I need to check for next, but here's my relationship setup:

                      Source:New_Database_dev2a

                      Table:Travel_Requests

                      Options:

                      Travel_Requests::pk_travel_request_id = TravReq_Acct_line_items::fk_travel_request_id

                       

                       

                      Source:New_Database_dev2a

                      Table:Accounts_Travel

                      Options:

                      Accounts_Travel::_pk_account_id = TravReq_Acct_line_items::fk_account_id

                       

                       

                      Source:New_Database_dev2a

                      Table:Accounts_Travel_Active

                      Options:

                      Accounts_Travel_Active::active = TravReq_Acct_line_items::active_travel_accts_constant

                      Accounts_Travel_Active::use_for_travel = TravReq_Acct_line_items::active_travel_accts_constant

                      • 8. Re: Conditional Value List using pop-up menu in portal
                        Extensitech

                        OK, I think I see it. Link your Accounts_Travel_Active directly to Travel_Requests, instead of via TravReq_Acct_line_items.

                         

                        From your latest clue, it sounds like you're entering the field in TravReq_Acct_line_items to create the new record, but since not yet exists, you can't get to Accounts_Travel_Active.

                         

                        Since the value list isn't dependent on any values in the line items except constants, you can get that just as easily from Travel_Requests, regardless of whether a line item exists yet or not.

                         

                        Chris Cain

                        Extensitech

                        • 9. Re: Conditional Value List using pop-up menu in portal
                          jdevans

                          This did the trick. Linking the accounts thru line items was a mistake. Doing it the other way makes it work every time, even if no existing portal records exist.

                           

                          Thanks!!