0

API Returns Array of Arrays (a table of values only)

We are working with BigTime as our time and expense tracking solution.  We are pulling information for project accounting, billable vs non-billable hours, resource allocations, and other information.  In the BigTime API, everything is presented as a report, with the Data portion represented as one JSON element called "Data" represented as an array of arrays (the table is an array containing an array for each row in the table).  Field names are represented as a second JSON element called "FieldList", which is standard JSON nomenclature.

The issue I am having is being able to subset, splice, or somehow separate the array elements.  The Data Source seems to recognize and show the array (only when the connector is configured as application/json, but not application/xml).  But even just showing the data source, or one row in a label in a Klip doesn't seem to work.

Any ideas?  I've tried @/Data; and @/Data[6] as well as @/Data[6][2], which are all syntactically correct according to the interface, but no values appear in any Klip controls.  I also can't represent a Modelled Data Source.  Here is an example of the "Data" and "FieldList" elements:

{"Data":[[5933413,"Client Q",5933413,0,"2021-02-22",23725,18037.5000,5687.5,7284.7450,138,137.7500,0.25],[5966806,"Client R",5966806,0,"2021-03-03",27000,7987.5000,19012.5,3225.8850,240,53.7500,186.25],[6011159,"Client S",6011159,0,"2021-03-23",9540,16997.5000,-7457.5,242.3200,70,132.0000,-62],[6091465,"Client T",6091465,0,"2021-04-26",30900,5625.0000,25275,2271.7500,202,43.7500,158.25],[6099168,"Client U",6099168,0,"2021-03-01",78410,262.5000,78147.5,106.0150,538,1.7500,536.25],[6099420,"Client V",6099420,0,"2021-04-29",2808,0.0000,2808,0.0000,18.75,0.0000,18.75],[6118486,"Client W",6118486,0,"2021-04-30",31525,11560.0000,19965,1877.9800,209,123.0000,86],[5027106,"Client X",5027106,1,"2020-03-02","",0.0000,0,0.0000,"",0.0000,0]],"FieldList":[{"FieldNm":"systemid","FieldType":"intType","Maxlen":8},{"FieldNm":"dname","FieldType":"varchar","Maxlen":1000},{"FieldNm":"dname_id","FieldType":"intType","Maxlen":8},{"FieldNm":"prjisinactive","FieldType":"intType","Maxlen":4},{"FieldNm":"prjstartdt","FieldType":"datetime","Maxlen":4},{"FieldNm":"esttotalfee","FieldType":"floatType","Maxlen":8},{"FieldNm":"chgin","FieldType":"floatType","Maxlen":17},{"FieldNm":"estfs_remain1","FieldType":"floatType","Maxlen":8},{"FieldNm":"chgcost","FieldType":"floatType","Maxlen":17},{"FieldNm":"esttotalhrs","FieldType":"floatType","Maxlen":8},{"FieldNm":"hrsin","FieldType":"floatType","Maxlen":17},{"FieldNm":"estfs_hrsleft1","FieldType":"floatType","Maxlen":8}],"IsEmpty":false}

1 comment

  • Avatar
    Shima Beigzadeh Official comment

    Hi Todd, 

    Thanks for reaching out! I have created one simple table for you to show how you can represent your data. In Klipfolio we have a function called
    kf:element_at that is used when a field name is unnamed and instead is referenced by number. 

    For column 2 or 3 you would simply change number 1 in @kf:element_at(Data,1) to 2,3,4, etc. 

    Hope that helps! 


    Shima

Please sign in to leave a comment.