About Zapier Community
New to our Community? Get to know more about our space and how to make the most of your time here.

Code of Conduct Community FAQ's Zapier Common Questions What to Expect

Get Started With Zapier
New to Zapier? We have got you covered with our Zapier 101 Resources.

Zapier Essentials

Get Support
Have a question? Let's get you an answer!

How Do I...? Troubleshooting Code & Webhooks Developer Zone

Other Help Resources
Wait, there's more! Check out these Community sourced spaces for additional resources.

Zapier Help Center Hire an Expert

Zapier Reading
All of our best learning resources can be found here.

Featured Articles Show & Tell AI Hub Zapier Tools Hub

Learning Groups
Excited to meet other builders? Check out our interest areas full of doers, dreamers, and in-betweeners.

User Groups

Other Learning Resources
Some of our favorite additional ways to upskill your Zapier knowledge.

Zapier Learn Zapier Blog Zapier Webinars

Product Updates
Check out what is new and upcoming with Zapier with our regular product updates.

Product Updates

Early Access Program
Want to join Early Access? Learn more here.

Best answer

Using wildcards in Utilities > Lookup table?

  • 19 December 2019
  • 4 replies
  • 779 views
AbbyD
Userlevel 3

Is it possible to use wildcards in the lookup table formatter?

Use case: I am integrating Splitwise and Google Sheets. Splitwise assigns ID numbers to categories. I want these to display as text (home, food, utilities, etc.) in Google Sheets.

However, Splitwise also has subcategories which it also numbers, but it uses the greater category as the first number. so if Food and Drink were the parent category and had an ID of 3, Groceries would be a sub-category under it with an ID of 31 (3 for food and drink, 1 for the first sub-category).

Is there a way to tell the lookup table to look for 3* (anything starting with 3) and assign the category to it?

Let me know if I can clarify my explanation or what I'm asking for. Thanks!


icon

Best answer by Danvers 20 December 2019, 08:53

View original
This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

4 replies

ChrisP
Userlevel 7
Badge

I think you need 2 lookup tables. In the first set for all 3 sub-categories to 3 (for example) and then use the output of the first lookup table for the second text lookup table.


Danvers
Userlevel 7
Badge +12

Hi Abby! It's not possible to use a wildcard in a Lookup table, sorry about that!

I think that you could take @ChrisP's idea but only use one Lookup table. You'll need to add a line in the lookup table for every sub-item, putting the categories for each one.

If that will be a lot of lines, one thing that you can do is to use a Google Sheet as a lookup table. If you can, export all of the subcategories into a sheet, and add the category of item for each. Then use a find row step instead of a Lookup table.

OR Are all of the subcatgories numbers (ie there are not letters in them?) and are they all the same length (eg they are all x number of digits long) because it they are then you could use the Spreadsheet style formula function (in Formatter -> Numbers). For that you could create an if/then statement that says something like: if the code is a number is between 1000 and 1999 then output Food and Drink, if it's between 2000 and 2999, then use the category Groceries, etc.

​I hope that helps, please let me know if you have any questions!


ChrisP
Userlevel 7
Badge

@Danvers You are right - 2 tables would be redundant. One other possible way of solving this would be to use a Formatter step and just strip the first digit out of the ID numbers and feeding that digit into the lookup table.


AbbyD
Userlevel 3

Hi all! Thank you for your replies. I ended up using the lookup table and defined each item. I think this would have met my needs, but then I found out the source doesn't assign numbers per sub-category consistently 🤨

Still grateful for your guidance!


Terms & Conditions