oData connection with a nested table to expand

Ask a question
1

I have an oData connection with a nested table ‘Details’ I need to expand.

https://view.myisolved.com/infin/odata/PayGroupPayrollBillingItems?$expand=Details

When using the $expand function Parabola is returning a message “It appears this data might not be fully expanded. If so, try using Top Level Keys or the JSON Flattener step to format your data.”.

I’ve tried the JSON step to no avail. Any suggestions?

2

Hi Joe – Welcome to the community :wave:

Unfortunately I am not able to open the link as I am receiving a 401 error message. Can you possibly please attach some screenshots of what you are seeing on your end?

3

2021-04-02_11-41-17
2021-04-02_11-41-171920×937 101 KB

4

Hey @Joe_Scola,

It looks like the API is returning the results as expected. If you make a request to this endpoint outside of Parabola, the API returns these results:

{
    "@odata.context": "https://view.myisolved.com/infin/odata/...",
    "value": [
        {
            "MonthId": 3,
            "YearId": 2021,
            "ServiceGroupId": 1,
            "Details": []
        }
    ]
}

It looks like you’ve already used value as a top-level key. However, the Details column is still returning a blank array.

Does oData show a different value as a Details property?

5

When I make the request using Excel or PowerBI there is a table in the details column containing the data i am trying to get to.

6

In Power BI this query = Source{[MonthId=3,YearId=2021]}[Details] expands into 21 columns of data. Do i need to select the columns I want to display?

7

Hey Joe,

Excel or PowerBI may parse the data in a slightly different way. However, the standard API request returns an empty array.

Is there a link you can share that has the complete API reference?

8

I am not sure what else you are looking for - here is the endpoint i am using

https://view.myisolved.com/infin/odata/PayGroupPayrollBillingItems/?$filter=YearId eq 2021 and MonthId eq 3&$expand=Details

The information I need is nested in the details table .

9

Hi Joe,

I’m looking to see if there are more detailed instructions on how to construct the endpoint. When testing that endpoint outside of Parabola, I’m using an API client tool like Postman.

The results I receive mirror the empty array returned in Parabola.

10

We don’t have any documentation on it. The odata is from our own application. Once i drill into the details table in powerBI there are columns ClientId, ClientCode, ClientName, CompanyId, CompanyCode, CompanyName, PayrollId, PayGroupName, PayDate, SystemBillingItemId, SystemBillingItemId, FlatAmount, ItemCount, DiscountAmount,

11

Hey @Joe_Scola

Can you send us a link to your flow at help@parabola.io? We’d like to take a look at your step settings from our end to see if there’s any additional information we can provide.

12

Thanks - I sent it this morning.

1 Like
13

I found the answer to this problem in case anyone else encounters it - the correct syntax

https://mycompanyodatasite.com/infin/odata/PayGroupPayrollBillingItems(MonthId=3,YearId=2021)/Details