Jump to content
  • 0
Chato

Zapier data integration question

Question

Hi,

I am having a basic issue that I am sure there's a way to handle.  Zapier is importing data into a structure that I am not sure how to transform.

See the below example. 

In the Caspio table that is integrated with Quickbooks Online, column A is a date. Column B lists my vendor name. Column C lists two dollar amounts. I have a record in Caspio as follows: 
Column A: 4/18/19
Column B: Joe Smith
Column C: $125; $50; 

How do I expand these rows so that these are distinct records?

Let's say I want to create a data page and only show records >$100 How do I do that? 

Or, if I want to show distinct rows in a data page for the above example. How do I show the following:

4/18/19 Joe Smith $125
4/18/19 Joe Smith $50

I want to show two different line items, whether in the table or in a DataPage.

Share this post


Link to post
Share on other sites

1 answer to this question

Recommended Posts

  • 0

Chato,

You could achieve it through tasks:

- create a table that acts as the staging area for transforming the records

- create a table that receives the transformed records

- create a task that checks the position of the ; in column c and substrings the first part into the final table as a new record and substrings the second part into the final table

This process would require a case statement in tasks where it checks position of ; and transforms the value accordingly. You would need multiple if statements to check position of ; and transform accordingly.

If import is daily, you could add where clause to select statement that only runs task on today's records (use difference of days between import timestamp and system timestamp = 0). You would need import timestamp added to your table for this.

I am using a similar process for a few apps of mine and found it works. If you want to go that route I can share some example task screens for how it would need to be setup.

If you are running any aggregate values in your apps off of this field you will want it transformed into numeric, single value only.

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...