How can we help?

New post
0

Dynamic Datasource

Avatar
Dale Pease
None
Follow

I've been struggling to set up a dynamic data source to connect Klipfolio to our CRM, ServiceBridge, using the documentation I've found on KF.

ServiceBridge will only allow us to pull 1,000 records at a time from a report using a URL like this:

https://cloud.servicebridge.com/api/v1.1/Report?name=JobsEstimatesAll-Klip&dateFromFilter=1%2F1%2F2019&dateToFilter={date.today}&pageSize=1000&page=1

I set up the first static data source, like above, and this returns the field: Total 29961, which is the total number of records (not pages.)

I've then set up a dynamic data source that looks like this:

https://cloud.servicebridge.com/api/v1.1/Report?name=JobsEstimatesAll-Klip&dateFromFilter=1%2F1%2F2019&dateToFilter={date.today}&pageSize=1000&page={props.pageNumber}

(I'm not sure what the "props" portion of this variable is for, or means.)

I've set up a variable called "pageNumber" under Advanced Tools within the Klip editor. I gave it a Klip Editor Value of 1.

Finally, I've created a formula, in the Formula Bar of a Klip like this:

MAPFLAT(NUMBERFORMAT((CUMULATIVE(REPEAT(1,@/Data/Total;))),
"pageNumber",
@/Data/Data/StatusName;))

I've set the properties to "Show as aggregated value" Count. This should basically give me a number equal to the total records, 29961. But, I'm getting no results.

If I analyze this portion: CUMULATIVE(REPEAT(1,@/Data/Total;)) it gives me the expected result (Showing first 300 of 29961 items) And if I analyze this portion: @/Data/Data/StatusName; it gives me: "Showing first 300 of 1000 items" So parts of this formula are working. It just doesn't seem to be paging through the pages (dynamically.) I understand I may need to do some math with the record count to get my actual number of pages, but right now I'm just trying to get this formula to give me some kind of expected result.

Date Votes

2 comments

  • 0
    Avatar
    Keely Davison

    Hi Dale,

    Thanks for your question on Dynamic data sources. 

    You are following the logic correctly and you are right you need to do some math to get the correct number of pages of data.

    Right now the formula is trying to get 29,961 pages instead of 30 pages. 

    So the first adjustment to make is to change the CUMULATIVE to be 

    CUMULATIVE(REPEAT(1, CEILING(@/Data/Total; / 1000)))

    Next to help with debugging you can use the Advanced Tool and change the page number to test values.  Change the pageNumber to 2

    Select the 

    @/Data/Data/StatusName; 

    part of your formula and evaluate it.  See if it gets a different 300 of 1000 items returned.  i.e. If you see values from page 2 of the data.

    Also if you evaluate the entire MAPFLAT you expect to get 300 or 29,961 values. 

    If you can not get it working after changing the Cumulative part of the formula, email support@klipfolio.com to create a ticket and we can help you specifically in your account. 

    Final note, "props" is a system keyword so that Klipfolio knows the next piece of information is a variable name.

    Keely

  • 0
    Avatar
    Dale Pease

    Thanks. Both of your suggestions work (as I expected them to.) The issue is I'm still getting 0 results when I run the whole formula. I'll submit to the support email. I'm new to Klipfolio, and I'm not quite sure what the best support avenue is, if it's email, that's great.

     

Please sign in to leave a comment.