Skip to main content
Question

multiple selection & if values match, show me the list belonging

  • November 3, 2017
  • 0 replies
  • 56 views
I have the following situation, where I would need assistance if possible, and there are two parts of this request.

Part 1. 
_I have one table where I have Product names and versions (one product may have more versions). In the next table I am trying to create a situation where when I choose a product from the list, I'd be offered only with the selection of versions belonging to that Product.
Example:
_Product X has versions 1.0; 1.5; 2.0, and Product Y has versions 2.0; 2.2; 3.0 when I select Product X, or Product Y in the next field I would have the choice of selecting only versions belonging to the product.

Part 2._
Part 2 is actually related to Part 1 in a matter that I would need to have a multiple choice, where I would have the opportunity to select 1, 2, 3 or 4 products and for each product to select the appropriate version. 

I would appreciate any help on this matter 
    This topic has been closed for replies.

    • ShawnRenn2
    • Registered
    • November 3, 2017
    The first part is easy!

    In the example you provided, you're describing a conditional drop-down, but you might need to add a few tables....

    You need 4 tables. Products, Versions, and Line Items, (and of course whatever table of records you'e adding these products to...for the sake of this walkthrough, we'll assume you're adding them to an "order" record.

    The first step is to build a relationship between "Orders" and the "line items" table where an Order can have many line items.

    This table serves one purpose... to be the connective tissue between a known list of products, and to allow the user to choose versions that product has. (much like an invoice, If you want the user to be able to indicate more than one "Unit" of a product/version type in a line, the only field the line items form would need is a numeric field called whatever you want to say how many are being ordered).

    Next, we're going to relate "products" to "versions" where the a Product record can have many versions. When building this relationship, it's important that you set a lookup field on the Versions table for the Product name, or whatever field you expect the user to choose in the first drop-down as you described.

    ***this gets a bit more complicated if 2 different products can share a "version" so we'll work on the assumption each product has it's own unique list of versions as in your given example.

    The next step is to relate both "Products" and "Versions" to "Line items",

    When you make this relationship, it's important that you send the Product Name down to the Versions that relate to it as we'll need this in the next step.

    Next relate "versions" to "Line items" where "versions" can appear on many line items. Be sure to include the "Version - Related Product" field when building this relationship as well...


    Once you have the relationship built, you should have a form on the line items table that includes the following fields, (assuming you didn't edit the default field names during the creation of the relationships) 

    [Related order] , [Related Product], [Related Version], [Related Version - Related Product] and of course the Quantity field if you want to indicate the # of units of a particular product/version being ordered).

    CONDITIONAL DROP-DOWNS
    You now have all the necessary fields, so you will edit the field properties for [Related Versions], and find the check box that says "The values in this field depend on a selection in another field"

    You'll be presented with 2 drop-downs. A selection in [Line Items - Related Product] Shows choices where "Line Items: Related Product" = [Version - Related Product].


    YOU'RE DONE!

    If you didn't already have separate products and versions table, you will need to manually relate/create the products and their appropriate versions. Once this is done, your second drop-down will only show choices for versions that are related to the product chosen.

    It's easier than this novel of instructions might make it seem. I just like to be thorough.

    Cheers!

    ***Update*** Part 2 of your original question could potentially be achieved by changing the embedded report for Line Items on the Orders Table a "Grid Edit" report. Thereby allowing you to select any number of products, and their subsequent versions, while still inheriting the conditional behavior you created....
    • ShawnRenn2
    • Registered
    • November 6, 2017


    I'm glad you found it helpful! Now for the "Grid Edit" part of my suggestion. Whenever you build a relationship between tables, the report on the parent table that shows the many child records associated with it is what's known as an embedded report. Like any other field, (and report for that matter) you can modify it's properties. If you right click the report, and select "Edit the properties of this form element", You'll have the option to make that report "Editable" from the Parent Record form...


    By making the embedded report editable, you are able manipulate/create lines of child records much like you might expect in Excel. (The example below is from the Complete Project Manager app in the Exchange)



    As you can see from the screenshot, existing child records can be changed here, and new child records can be added. **Note, as you populate the "empty" lines, new rows will be created to take their place, so don't get the impression from the screenshot that you'd be limited to only 4 "new" records at a time.

    Hope this helps.
    • NenadIlic
    • Author
    • Registered
    • November 6, 2017
    Yup, that does the trick :)
    one additional question, if its not a problem...

    Since Product Name is a drop down field, is there a way to define what to show in the picker, since now I'm having excessive information:
    Product # 3 - Product Group 1 - Product name (so practically this makes it difficult to choose)?
    • ShawnRenn2
    • Registered
    • November 6, 2017


    Yes! To change the (up to 3 fields) that you see in any drop-down, navigate to table that drop-down represents. (in our case "Projects") and click "Settings", then "Advanced Settings".

    Look for the section called "Identifying Records" and you'll see 3 dropdown. If all you want to display is the product name, choose this in "Column 1" and set the other two to empty, or rather the "Select a Field" option, like below...Then click Save.



    Good luck!
    Terms & ConditionsAccessibility statement