Pulling drop-down menu items from second list, then modify item in second list when selected in drop-down


  • Using two lists: Manage Student Testing (MST), Available Test Vouchers (ATV).  Several teachers will assign vouchers when entering student test data.  These vouchers numbers are unique (and expensive) and must not be used twice.  Drop-down list in MST list is set to "Enforce unique values:" to prevent duplicate selection.

    Looking for a way to populate a drop-down field in MST with only vouchers that have not been assigned and then in ATV change the voucher assigned field to Yes when the voucher is used in MST.

    Currently have a drop-down list in MST that pulls options from second list ATV.

      a. How can I make the MST drop-down field only pull vouchers with a status of not assigned in ATV?

      b. How can I automatically update the AST voucher status to assigned "Yes" when assigned to a student and saved in MST

    What would be the best practice or strategy to accomplish this with SharePoint, InfoPath and/or SharePoint Designer?

    Thank you for your advice and direction.

    Monday, February 6, 2017 1:44 PM

All replies

  • Hi Jeff- looks like you have two "issues" to resolve: one being a cascading lookup type dropdown and another to set a field's value based on the value of another column. There are a couple ways you can go about this.

    It's best to use either InfoPath (or another form program) to accomplish both of your goals. Or you could use a third party tool like Kwizcom for your cascading lookup. You may also use a SP Designer workflow to set the field's value as an alternative.

    So, after all that, I highly suggest you use InfoPath to solve both needs. However, keep in mind that InfoPath won't be around forever. You may check into a paid form program like Nintex if you have the funds.

    cameron rautmann

    Monday, February 6, 2017 8:00 PM
  • Thank you for the input
    Tuesday, February 7, 2017 1:48 PM
  • Part A = Solved in InfoPath<o:p></o:p>

    Drop-Down List Box Properties > Add Data Source > Entries > Select a Field or
    Group (select field to populate list) > Filter Data (button) > Add
    (select field and criteria for filtering list)<o:p></o:p>

    In my example, I'm only pulling Available Vouchers where the Assigned to Student
    column is false.


    Now for Part B - How to change the status of the Assigned to Student value from False
    to True  in secondary list on form submit?<o:p></o:p>

    I'm attempting to create a rule on the Drop-down list box that will Set a field's
    value in a secondary list field: when field in drop-down is not blank.

    Rule doesn't throw any errors with Design Checker in InfoPath, but when I add an
    item in SharePoint list via form nothing changes in the secondary list Assigned to Student field when I
    select a value from the Drop-down list box or when I Submit the new item.<o:p></o:p>

    I have pretty screenshots for example, but I'm not an "Authenticated User" so I hope my text will suffice.

    <insert gif of me banging head on wall>

    Wednesday, February 8, 2017 12:45 PM
  • You're a funny man! For the second part, you could go about it in a couple ways: create another data connection to submit the value to the field in the list, or an easier way would just be to create a lookup column from the ATV list to the MST list using a column that contains the value of Assigned to Student field (create that field in the MST list instead). Set that field's value in InfoPath.

    cameron rautmann

    Wednesday, February 8, 2017 1:55 PM