Zapier data integration question

[Chato]

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.

[ChrisCarlson]

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.

 

[Flowers4Algernon]

Hello! Just wanted to share Caspio's new feature that you may also want to look into: 

https://howto.caspio.com/webhooks/webhooks-overview/