Logbook-query-v2: REST API using power BI - j5 - 30 - Reference - Hexagon

To start, create a new blank query in Power BI. Copy the following code into the query using the advanced editor. This code fetches the data from the server, regardless of the number of pages in the result and combines them all into a single output table.

let

FetchData = (url as text) as table =>

let

all_values = List.Generate(

() => [items = {}, url = url],

each [items] <> null,

each if _[url] <> null then [

contents = Web.Contents(_[url]),

json = Json.Document(contents),

items = json[values],

url = json[nextLink]

] else [

items = null,

url = null

],

each [items]

),

combined = List.Combine(all_values),

table = Table.FromList(combined, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in

table

in

FetchData

This query can then be called with the URL representing the logbook query that you would like.

You are prompted for your j5 user name and password the first time you run the query.

• It is not possible to get the last modified datetime for a whole IndustraForm, the function only works for a specific cell. If you want to return this information using the REST API, you need to retrieve it within the IndustraForm (as a hidden cell) and then pull that cell.

• It is not possible to get the datetime a section was submitted or the user who did the submission.

Example queries

1. Retrieves the Permit Titles and Permit No of all the permits stored in the j5 Enterprise demo site

   https://[j5 Server URL]/restserver/30/industraform/logbook-query-v2/permits?attribute_names=Text2,PermitNo

2. Returns the Permit No and the number of sections waiting for submission for all the permits in the j5 Enterprise demo site

   https://[j5 Server URL]/restserver/30/industraform/logbook-query-v2/permits?attribute_names=PermitNo,$Form.SectionsWaitingForSubmission

3. As above, but limiting the number of records to 4.

   https://[j5 Server URL]/restserver/30/industraform/logbook-query-v2/permits?attribute_names=PermitNo,$Form.SectionsWaitingForSubmission&top=4

4. Fetches the Status of all the Work Overview records that have a Start Time between 2019-09-01 and 2019-09-12

   https://[j5 Server URL]/restserver/30/industraform/logbook-query-v2/work_overview?attribute_names=status&prettv=true&from_date_time=2019-09-01T00:00:00Z&to_date_time=2019-09-12T00:00:00Z