Forum Discussion
Use existing table column to loop through each id and create a new table with API
Im struggling to create a function and then loop through the IDs of an exisiting table to feed into the API call.
This is the function (which works fine):
//GetTicketTimeEntries
(TicketID as number) =>
let
Source = Json.Document(Web.Contents(FreshserviceBaseURL,
[RelativePath="/tickets/" & Number.ToText(TicketID) & "/time_entries",
Headers=[Authorization=FreshserviceAuthHeader]]))
in
Sourceand this is the table query:
//AllTimeEntries
let
Records = List.Generate(()=>
[Source = GetTicketTimeEntries(1), id=1],
each List.Count([Source][time_entries]) > 0,
each [Source = TicketID ([id]+1), TicketID=[id] +1],
each [Source])
in
RecordsI cant figure out how to insert the source to loop through being the TicketIDs of the All Tickets table, id column.
Not sure what you need the second query for. Can't you just use the JSON from the API call directly? Or is there a typo in the
each [Source = TicketID ([id]+1), TicketID=[id] +1],line?
Yeah, im totally confused to be honest. Are you saying use the ListGenerate function in the first query?
I would assume you get a JSON array back and you shoud be able to convert that into a table for each ticket. Then at the end you just expand that column to concatenate all the tables.
Using Postman, when I do a GET /api/v2/tickets/19302/, the JSON returns all ticket details but has no data on time entries. To get the time entries, i need to include time_entries like this api/v2/tickets/19302/time_entries
Service Desk API for Developers | Freshservice
//GET 'https://domain.freshservice.com/api/v2/tickets/20' { "ticket": { "cc_emails": [], "fwd_emails": [], "reply_cc_emails": [], "fr_escalated": false, "spam": false, "email_config_id": null, "group_id": null, "priority": 3, "requester_id": 1000000678, "requested_for_id": 1000000670, "responder_id": null, "source": 2, "status": 2, "subject": "Ticket Title", "to_emails": null, "sla_policy_id": 1000000029, "department_id": null, "id": 20, "type": "Incident", "due_by": "2017-09-08T23:03:44Z", "fr_due_by": "2017-09-08T15:03:44Z", "is_escalated": false, "description": "<div>this is a sample ticket</div>", "description_text": "this is a sample ticket", "custom_fields": { "custom_text": null, "auto_checkbox": false }, "created_at": "2017-09-08T11:03:44Z", "updated_at": "2017-09-08T11:37:01Z", "urgency": 1, "impact": 1, "category": null, "sub_category": null, "item_category": null, "deleted": false, "resolution_notes":null, "resolution_notes_html":null, "attachments": [ { "content_type": "text/plain", "size": 5, "name": "attachment.txt", "attachment_url": "https://cdn.freshservice/data/Helpdesk/attachments/production/19852343/original/attachment.txt", "created_at": "2017-09-08T11:03:45Z", "updated_at": "2017-09-08T11:03:45Z" } ], "workspace_id": 3, "created_within_business_hours": false, "approval_status": 4, "approval_status_name": "Not Requested" } } //GET 'https://domain.freshservice.com/api/v2/tickets/20/time_entries' { "time_entries": [ { "id": 901, "created_at": "2019-07-19T10:17:23Z", "updated_at": "2019-07-19T13:18:40Z", "start_time": "2019-07-19T13:18:09Z", "timer_running": false, "billable": true, "time_spent": "03:00", "executed_at": "2019-07-18T18:30:00Z", "task_id": null, "workspace_id": 3, "note": "time entry 1", "agent_id": 1, "custom_fields": {} }, { "id": 902, "created_at": "2019-07-19T10:18:23Z", "updated_at": "2019-07-19T13:19:40Z", "start_time": "2019-07-19T13:19:09Z", "timer_running": false, "billable": true, "time_spent": "03:00", "executed_at": "2019-07-18T18:30:00Z", "task_id": null, "workspace_id": 3, "note": "time entry 2", "agent_id": 1, "custom_fields": {} } ] }So my thought was i need to create a new table for all time entries and link that table with the Tickets table using the ticket ID.