1 Reply Latest reply on Jan 19, 2016 6:52 PM by macwombat

    Different type of a conditional value list (details / diagram within)

    facaylar

      Hi again. First off, I just want to thank y'all for the incredible help given as I've worked on one of my most involved databases to date.

       

      I'm hoping I can get another simple breadcrumb to arrive at a solution.

       

      Background: In this scenario, I have two layouts: layout #1 (Shipments List) and layout #2 (Mailing Queue).

       

      The Shipments List contains all the shipment information for a client with each separate shipment as its own unique record. One field in the list is the "Shipment Status" field. It is a pop-up menu that is part of a value list which consists of: New, Processing, Hold, Ready To Ship, Cancelled.

       

      In the Mailing Queue, there is only one field titled "Shipment Status". This field is also a value list containing the same values as the shipments list pop-up in the Shipments layout: New, Processing, Hold, Ready To Ship, Cancelled, with the addition of another value: All.

       

      Goal: To allow staff to quickly see what shipments exist under each status, I am trying to make the Mailing Queue pop-up dynamically show all related shipments that match said status. This information will be shown in a portal within the Mailing Queue layout, and it is updated dynamically. For example: the user will select "New" on the pop-up and the portal displays all associated shipment records that have the matching "New" status. Selecting "Hold" shows matching "Hold" and so on and so forth.

       

       

      The Problem: There is a sixth value in the Mailing Queue value list: All. When a user selects this, I am indicating to the database that all statuses are selected and therefore show all associated records that contain the five aforementioned values of New, Processing, Hold, Ready To Ship, and Cancelled ('All' is the default status until one of the other statuses is selected by the users (easy 'Case' solution)).

       

       

      Solutions Attempted: The Shipments and Mailing Queue layouts have a relationship defined wherein Shipments__RequestStatus=MailingQueue__RequestStatus. The portal does a perfect job of displaying associated shipments that match the Request Status in the Queue, but I am missing part of the logical puzzle in getting it to display all records in the Shipments layout when 'All' is selected. I have also tried (for kicks and giggles) creating a simple text calculation field to provide the following: GetAsText ( Request Status) & " All" and mapping that to a modified value list that has 'New All', 'Processing All', etc. (Disclaimer: I don't know why I did this, but I felt like it was a part of a solution).

       

      Here is a small visualisation of how it is currently set up (I forgot to draw the relationship in, but Shipment Status is the common field in both layouts.

       

      SGTbJ6Z.jpg

       

       

      Any help would (again) be greatly appreciated.

        • 1. Re: Different type of a conditional value list (details / diagram within)
          macwombat

          Hi.  You were heading along the right line with your text calculation field.

           

          In the shipments table create a new field - call it whatever you like - eg. "Shipment Multikey" as a calculation field with calculation:

          List ( Shipment Status ) & "¶" & "All"

           

          If you aren't using the relationship between shipments and mailing queue to do anything else then change the predicate in the relationship (or create a new table occurrence and relationship if you need to).

           

          Shipments List::Shipment Multikey = Mailing Queue::Shipment Status

           

          (if you are creating a new table occurrence then you will need to base your layout on that new occurrence).

           

          HTH, Chris