Refining values displayed in a dropdown

Question

We have a field called Status with 10 values like (New, In Progress, Approved, Prod Completed, Env In Progress, Env Completed).

We wanted to define a work flow such that only next value will be displayed based on the saved values.

So when the status is New, the status dropdown should have the value 'New and 'In Progress' and nothing else. Similarly if status is 'In Progress', dropdown should have 'In Progress' and 'Approved'

New -> In Progress -> Approved -> Prod Completed -> Env In Progress -> Env Completed

Answer 1

Hello,

what tools are you prepared to use? InfoPath? JavaScript? 

The following should work: Create two lists in SharePoint. The first one lists the status values and numbers them

value	stage
New	1
in progress	2
approved	3
prod completed	4
env in progress	5
env completed	6

Another list has the stage numbers and which dropdown values to show for each stage, like this:

Stage	dropdownValue
1	New
1	in progress
2	in progress
2	approved
3	approved
3	prod completed
4	prod completed
4	env in progress
5	env in progress
5	env completed
6	env completed

If you use InfoPath to customize the form, you can look up the stage number of the current value in the field, and then use that stage number to filter the dropdown list to show only values for the stage number.

---

cheers, teylyn

Answer 2

Thank you Teylyn!

I created two list as mentioned above (List 1 Status Values and List 2 Status mapping).

In InfoPath, for the Status field dropdown, i created external data source as Status mapping list and created filter values for each stage.

Then i was trying to create rule for Status filed to populate list based on current value as follows:

Condition: Status = "New"

Action: Set a field's value and i selected Status field. But i am unable to find the Status mapping list.

Am i following the right steps for filtering the data?

Thank you,

VJ

Answer 3

Sorry for the silence. I was offline for a few days.

You're on the right track.

Here are a few screenshots. The default value for the Status field is "New".

There are two data connections: StatusList (which is the first list I mention above) and StatusStage (where stage values are mapped to the text values, the second list I mention above).

Do not load any data by default. The lists will be queried when the form is opened. This is done with rules in Data > Form Load. Ad an action rule with these actions:

Set a field's value (StatusList query field "Title" to value of the Main status field)

Query the StatusList data connection.

Then Set a field's value ( StatusStage query field "Stage" to value of the StatusList "Stage" field)

Query the StatusStage data connection. 

Now you assign the Status dropdown the StatusStage data source and you're done. The form in the following screenshot shows the two secondary data sources pulled onto the canvas. 

With this approach you don't need any filtering on the drop down, since the data source will only contain the items that match the query.

---

cheers, teylyn

Answer 4

Thank you for detailed explanation Teylyn!

I followed the above step but dropdown is fetching all the values in Status Stage list. Is there a way i can identify where the issue is?

Answer 5

Hi,

here is what I do for troubleshooting: 

I have the secondary data sources on the canvas during testing, so I can see what's going on.

I double check that I am using the correct field names. Sometimes, when an original SharePoint column (like "Title") has been renamed, it still appears as "Title" in the IP formulas. That can lead to confusion.

Double check that the data connections are queried.

I create a number of text fields for testing. After each action, I write a status message into a text field.

---

cheers, teylyn

Answer 6

Hi,

As suggested, i verified the column names. it is correct. But for some reason, the query is not fetching the right data.

For status field, i have mapped the StatusStage list as Data Source (This was done by right click -> Dropdown listbox properties -> Get Choices from an external data source -> Select StatusStage from the dropdown). I didnot modify the rest of the fields which got populated automatically Entries, values and display names

Form Load rule:

Set field's value: Stage = 2   -> Stage is StatusStage

Query using a data connection: StatusStage

I was trying to check whether the query is working by filtering the value for stage 2 alone. But the Status dropdown displays all the values and not just the Titles which has Stage as 2.

Is the above rules correct?

Answer 7

Uhm, there are four actions in the rule. 

Set the query field for StatusList to the current status

Query StatusList (this will return one item only. You need the stage number from that item)

Set the query field for StatusStage to the number from StatusList

Query StatusStage

---

cheers, teylyn

Answer 8

Initially, I have created Form Load rules as follows:

   Set a field's value: Title = Status (Title is StatusList and Status is actual field in form)

   Query using a data connection: StatusList

   Set a field's value: Stage = Stage (L.H.S is StatusStage's Stage field and R.H.S is StatusList's Stage field)

   Query using a data connection: StatusStage

Then the DataSource for Status field is set to 'StatusStage'.

By following the above steps all the values in StatusStage list is displayed.

So, as you suggested, i was trying to check whether the query is working by creating a simple one as follows:

   Set field's value: Stage = 2   -> Stage is StatusStage

   Query using a data connection: StatusStage

Even in the above case all the values in StatusStage is displayed. But it should fetch only one value. So I was trying to figure out where the issue is. Im a beginner trying to learn and complete the customization. Thank you for understanding and helping me.

Answer 9

Are you setting the field in the correct node? Before you query a data connection, you must set the field in the queryFields node, NOT in the dataFields node.

If your query returns all values, it looks like you set the dataField and not the queryField.

---

cheers, teylyn

Answer 10

Thanks a lot Teylyn!!

You were right. I was setting it in DataField. Awesome!! it works now :)