How to Use a Column Name as Variable in a Workflow


Badge +5

I don't know if this is possible so please let me know. Here's what i'm trying to accomplish.

 

I have two lists within the same site - List A & List B.

 

List A contains three columns: Name, Week, Information.

List B contains four columns: Name, Week 11/5/15, Week 11/16/15, Received

 

A user will come into List A, give their name, select the week (ex. 11/5/15) from a pre-populated drop down menu, and save.

 

A workflow should then run that does the following:

  1. Find the user's name in List B (a row)
  2. Find the appropriate column date (that matches what the user selected in List A)
  3. Change the value for that row/column intersection to "Received"

 

Basically if someone submits something in List A, I want to mark them as being compliant in List B.

 

I understand how to use a collection variable to sort through IDs and assign variables to test BUT i'm wondering how i can get the workflow to recognize what column it should update.

 

If my users "Week" is stored in the variable weekOf, i need to be able to see which column name it matches.

 

Is this possible? Is there a way to dynamically pull the column titles of a list and use it in a workflow?


12 replies

Userlevel 6
Badge +16

You could get the list of columns in your list via a REST call

using this method >>> https://msdn.microsoft.com/en-us/library/office/dn531433.aspx#bk_ListCollectionGetByTitle

Badge +5

I have no idea how to implement that in a workflow. Do you know of any samples?

Badge +2

Hi Candace,

if you only have these two choices, it would probably be better to use a simple If or Switch action, to decide which option has been selected, like this:

Nintex Switch.png

The option Fernando talked about is using the SharePoint native webservices, which offer a lot more options, but are also more complex.

You could do this using the following steps as a rough outline (but I really recommend the first option as it is much simpler):

  • Create a list with the week values you want the user to select
  • Add a second column (text), in which you add the internal name of the column you want to set in List B for this value
    • an item in this list would look like this:
      • Item A:
        • Column 'Title': 11/5/15
        • Column 'FieldName': Internal name of column "Week 11/5/15"
      • Item B:
        • Column 'Title': 11/16/15
        • Column 'FieldName': Internal name of column "Week 11/16/15"
  • Change the type of the week column in List A to Lookup of this new list
  • In the workflow, use Query list to get the ID of the item in List B you want to update
  • Build an XML string according to this documentation (https://msdn.microsoft.com/en-us/library/lists.lists.updatelistitems%28v=office.12%29.aspx ) to update the list item you want
    • you retrieve the fitting column name from the newly created lookup list to set the field you want
  • Use the Call web service action to call the lists.asmx webservice method UpdateListItems

Hope this helps!

Badge +4

You might try separating out the weeks into sub folders within the list instead of trying to make them columns especially if you'll be dynamically adding additional columns, it would be far easier to dynamically add a folder then a column and it could be used as a dataset.

first find the date folder that's appropriate, build the URL based on a format you choose:

webURL/list/folder/...

Then read the items in the list located in that folder. Just another way to skin the cat...I'm sure there's other ways, but that seems far simpler then messing around with workflows, queries and column names especially if you'll be adding more columns dynamically later. This method can scale up as you go very easily.

Badge +5

There will actually be more columns in List B. There would be a column for each upcoming week. So the number of columns is not certain. Although the column name will be added manually each week.Martin Zierler

Badge +5

The end user needs to be able to quickly view if the employees have submitted for the week. I would really like to keep this layout where the room numbers and names stay the same but each week a new column is added. When the user submits via the other form, the cell for that user and week is updated to Received

screenshot.jpg.

Badge +5

Anyone able to provide guidance on this? I'm super lost.

Badge +4

I would have to venture that "creating a column" would not be best practice. Most of the time when you want to create a list you keep the structure of that list static. The data within those columns would be dynamic and you would build your structure accordingly to be able to handle whatever data is in it. If you had a SQL database, you wouldn't add columns to that post-Production release. you'd modify your structure only if needed because (usually) changing database structure requires change requests and other red tape. Nobody likes that so you should design the structure so you can obtain data and dynamically control it with that structure.

Reading over you original post, you're asking "is this possible?" Yes but why would you do this in the first place?

From the looks of it, you just need to create an excel spreadsheet and manually add the columns in each week as users make it and store that spreadsheet in a document library.

If it was me, I'd have a list that has all the weeks of data:

WeekuserRoomreceived
11123y
12234y
13345y
14456n
21123y
22234y
23345n
24456y
31123y
32234n
33345y
34456y

Then create a view that would filter by week, copy paste into excel and send that report. Maybe someone can assist with creating THAT in a workflow over you trying to manipulate your data structure each week to add more columns. Views and filters are where SharePoint shines. You should make use of those instead of trying to force a list into a report.

You could even create a view that would be filtered by the "received" column where you could display all users that you have not received reports from...or you could filter by the room where you could see which week you haven't got the room info yet and which user is responsible for it.

The whole method of adding a column just isn't really best practice. A list is a dataset, and it should be treated as such, not treated as a reporting tool. That may be where the difficulty here lies. You're really not using the list for what its intended to do: Store data.

Badge +5

Thanks for the suggestion. Let me give you a little background. The user was originally recieving 20 or so reports each week, then updating an excel sheet - similar to the one you were talking about.

The goal was to automate that process.

So the first step was to create a list in SharePoint where users could submit their weekly reports.

But now it's the manual part of tracking who has submitted.

I'm totally fine with creating the columns manually in advance but i would still like to see the list updated.

Take a look at this image i've attached.

Do you get what i'm trying to do now?

the-plan.jpg

Badge +4

If your ultimate goal is to see who hasn't reported each week it would be far simpler to setup a weekly scheduled task that would email the weekly summary of who hasn't submitted (received = "N" and week = <X>). That could easily be done within Nintex forms.

I guess the real question is what is the ultimate objective? Is it to make a report that shows ALL your data, or get a weekly update of what has been done?

Sounds to me like you're wanting to automate a process that needs to be automated in a different way. You're trying to build a report that you don't REALLY need anymore. The automated process could be something that compiles the information you need each week from the list and send it to whoever needs it. The overall report of everything sounds extremely bloated.

Badge +5

Yea, perhaps i'm trying to keep her old Excel view (in list two) when really the data should be saved in another way. I think the user really wants to see ALL of the data in one view. (maybe every 2 months they can delete old content).  I think i'll try your approach of adding list items instead of trying to update existing columns. Sending an report via email the pulls from the list may also work.

Thanks for giving me some things to think about! I'll have to reconsider my approach!

Badge +4

if the goal is to get ALL their data, in one spot and viewable they're wasting the power of sharepoint. Ultimately when you want data, they're looking for specific data they want to see. SharePoint views can filter their entire data set into manageable chunks that they can logically view.

View by Received reports

View by Non-Received reports

View by Week

View by user

View by Room

Those 5 views would probably give them all the data they really NEED, vs a huge raw dataset that becomes bloated over the long run and it can be setup to send them that data daily, weekly, monthly, hourly, minutely... That's the power of sharepoint and where it is best used. You can always export the data manually like they had, and if they really want that level of data, then they're OLD method may be the best one, but I think if you can filter the data down to a more manageable set of chunks for them, they'll realize that they don't really want all the data. They just want to view areas, and you need to teach them how to get what they really NEED vs what they WANT.

We just made a similar transition here and the business types went kicking and screaming (they love their spreadsheets) until I showed them how to obtain the data they wanted. Now they can't imagine how they worked in the huge spreadsheets they had before and their data is real-time which is what they REALLY wanted -that is the best selling point of SharePoint to any customer.

Reply