Skip to main content

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

Learning Resources
All of our best learning resources can be found here.

Featured Articles Show & Tell Zapier's products AI Hub Zapier Tools Hub

Share your knowledge
Give back to the Community by sharing your automation wins and helping to answer questions.

Share your success See unanswered questions 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?

  • December 19, 2019
  • 4 replies
  • 874 views
AbbyD

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!


Best answer by DanversBest answer by Danvers

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!


View original
Did this topic help you find an answer to your question?
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
Forum|alt.badge.img
  • ChrisPConnector
  • Connector
  • 79 replies
  • December 20, 2019

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.


AbbyD
1 person likes this
  • AbbyD
    AbbyD
Danvers
Forum|alt.badge.img+12
  • DanversZapier Staff
  • Zapier Staff
  • 3731 replies
  • Answer
  • December 20, 2019

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
Forum|alt.badge.img
  • ChrisPConnector
  • Connector
  • 79 replies
  • December 20, 2019

@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
1 person likes this
  • AbbyD
    AbbyD
AbbyD
  • AbbyD
  • Author
  • 1 reply
  • December 23, 2019

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