Building that parsing logic can be quite tedious, but the logic can be repeated with some Copy+Paste and should be tested right away. The parsing will have to be adjusted according to the structure of the Chart of Accounts being extracted. There could be more levels in other instances. I will therefore parse the rows up to 3 times before getting to the transactions with $ indicators. In my QB sandbox, the Chart of Accounts comprises of 3 levels of accounts. This will require further parsing of the headers from the row, filtering and rebuild of rows with columns, by following this logic: However, looking further at the output of the Rows, the 6,997 records in this case, you will realize it contains more levels of columns. The next steps is to obtain a single row per record with multiple columns, which can be done using simply a Cross Tab tool: Using filters, I will break down the data source into multiple tables, each reflecting a different level, starting with Report Headers, then Column Headers and then Rows: We will recourse to the Text to Column tool to convert the content of the JSON into several tables to break that structure and ultimately join them into a single flat table: Flattening Strategy This is very misleading, as this is in fact a VERY nested JSON data structure, comprising several tables as illustrated here in the number of segments between dots separating them: The Alteryx Connector will output what looks like a simple JSON file, with only 2 columns and 7,050 rows in my case: For instance, the amount indicator can be either: subt_nat_amount, subt_nat_home_amount, subt_nat_amount_nt or subt_nat_amount_home_nt depending on your instance, and the QB API will not help you, nor return any error message if you pick the wrong one… Trial & Error is your friend… If you don’t see your Amount or Balance in the extract, it is probably because you need to adjust those parameters to pick the other set of indicators. Parameters: Described here for the General Ledger report, they let you pick which columns to include, which time frame and most important, which indicators, depending on the multi currency settings of your QB instance.Realm ID also called Company ID in some parts of the documentation.Endpoint URL as mentioned earlier, can be either or for Production.Whichever tool you end up using to extract the raw data out of the QB API, you will need to customize the proper URL to get what you need. You can uncheck the first time option once you ran successfully the first query. You need to check the first time retrieving option to write the Refresh Token locally to your machine and obtain an access token that will be used to run the query the refresh token will get replaced and overwritten by a new value, written locally as well. Configure the connector with your credentials, highlighted is the Realm ID:.Download Alteryx QB Connector from here.Use those credentials in the QB connector.Note the mention that it expires in 101 days, so if you don’t run your workflow within those 101 days, you will need to return to that Oauth playground to regenerate a Refresh token Client Secret from the created App page.that Realm ID will be included in the URL endpoint.Ī nice trick to find it, when anywhere in your QB instance, is to press CTRL+ALT+/ and the Company ID will pop up, ready to be copied… The Realm ID is assigned to a company by Intuit when a QuickBooks Online user creates a company. Realm ID ( =Company ID) to uniquely identify the data of your QuickBooks company.check Accounting Scope and click Get Auth Code.The full documentation is here, but we can take some shortcuts this way: Now let’s focus on API access for your data.Note also that there are two distinct Base URLs depending on what you will use:.To understand how the API handles the GL report, go to the API explorer here and see how the report data is comprised of 3 levels:.If you don’t have an actual instance of QuickBooks to connect to, you can create a sandbox here:.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |