cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
koolgeex
Nintex Newbie

How to Use a Column Name as Variable in a Workflow

Jump to solution

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?

Labels: (2)
0 Kudos
Reply
12 Replies
fhunth
Nintex Newbie

Re: How to Use a Column Name as Variable in a Workflow

Jump to solution

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

0 Kudos
Reply
koolgeex
Nintex Newbie

Re: How to Use a Column Name as Variable in a Workflow

Jump to solution

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

0 Kudos
Reply
mzi
Nintex Newbie

Re: How to Use a Column Name as Variable in a Workflow

Jump to solution

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!

0 Kudos
Reply
waltont
Nintex Newbie

Re: How to Use a Column Name as Variable in a Workflow

Jump to solution

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.

0 Kudos
Reply
koolgeex
Nintex Newbie

Re: How to Use a Column Name as Variable in a Workflow

Jump to solution

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

0 Kudos
Reply
koolgeex
Nintex Newbie

Re: How to Use a Column Name as Variable in a Workflow

Jump to solution

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.

0 Kudos
Reply
koolgeex
Nintex Newbie

Re: How to Use a Column Name as Variable in a Workflow

Jump to solution

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

0 Kudos
Reply
waltont
Nintex Newbie

Re: How to Use a Column Name as Variable in a Workflow

Jump to solution

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.

0 Kudos
Reply
koolgeex
Nintex Newbie

Re: How to Use a Column Name as Variable in a Workflow

Jump to solution

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

0 Kudos
Reply