Updating Dropdown Custom Field in Clickup based on value in Microsoft Excel

[JerrodKetchersid]

Current Setup: My project management team works strictly with Microsoft Excel when it comes to their log communications with our factory and meeting sheets for internal teams. A few years ago, I integrated Clickup into our workflow to track projects at a more granular level. Project Management is only interested in tracking dates for project milestones (of which we have about 20) and not so much the 300 or so tasks under them we currently have in Clickup for each project. To solve this, I’ve tied the various logs and meeting sheets to a single Master Schedule spreadsheet and use that to calculate due dates for the individual task due dates. I use Make to funnel those task due dates into Clickup whenever the milestone dates are updated via the log communications.

My current scenario works great to pull in the Start Date, Due Date, In Review Date (Custom Field), and Push Date (Custom Field).

The Problem:
The value in cell G5 is currently a formula that states if Today’s Date is within 5 days of the “Cert Owner’s Guide” Milestone, it will display the date found in the “Cert Owner’s Guide” Milestone (Located around G201)…otherwise it will display the date in the “Final Owner’s Guide” Milestone (located around G241). The formula does what it’s intended, but there’s nothing for the person assigned to the task to know which milestone the date is pointing to. We do have an Artwork Type custom field that is a dropdown and has around 13 different artwork types in Clickup. I have another formula in K5 that changes the Artwork Type depending on which milestone the value in G5 is pointing to.

The Goal: I need to add a component to the scenario for that one row that looks at cell K5 and updates the Artwork Type between “Cert Owner’s Guide” and “Owner’s Guide” based on the value in the cell. I’m not sure where to get started. Any help is appreciated!

[Princy]

Since you have only two components i will suggest to use if function or switch function. They will work best in your this case.

Princy Jain
Make consultant,
For consulting support: Follow up Automated

[JerrodKetchersid]

Thanks, @Princy.

I’m not sure how that would look within the Make scenario itself, though? What modules need to be added? How should those modules be set up? I found a post from @Bjorn.drivn last year that MIGHT be close to what I’m looking for (Modify a custom field in ClickUp which uses UUID (dropdown, label etc.)) but I’m not doing something right.

Below is my scenario stream:

My issue arises from the below, though:

I named the variables that I wanted to map within an If statement.

And then attempted the If statement in the field below.

Do you have any suggestions?

[JerrodKetchersid]

Keeping the conversation going. Hoping someone might have some advice to share!

Thanks,

Jerrod

[Bjorn.drivn]

Hey @JerrodKetchersid ,

You are close to the solution I think, but

• most likely your map() + get() function is not working properly (or most stable). What is the output of the array aggregator? And what is the output of your set variable?
• Also, your if() function is not working properly. The = should be in green if its a function, you can use {{=}} to make this work. You also don’t need to use quotation marks for the if statement

Please share some more outputs etc. so it allows us to help you
~Bjorn

[JerrodKetchersid]

Thanks to a very informative phone call with Bjorn…this has been answered!

Once the “Search Workbooks” module runs, it’s routed (other branches not shown) to a module that pulls all accessible custom fields in Clickup. An array aggregator collects the Custom Field ID, Custom Field Name and Type Config. I then retrieve the relevant rows from Excel and populate the custom field using the following formula: