Exact Online, Yuki and Teamleader improvements

Wiki (en)
, , , , ,
1

During the holiday season a number of changes have been made by those unfortonate to be still at work.

Today’s release of Invantive Bridge Online and Invantive Cloud incorporates a number of bug fixes to solve hanging downloads of data, plus various enhancements on Yuki, Exact Online and Teamleader.

Exact Online

On Exact Online, the new fast and incremental download of quotations has been made available. You can use it from Power BI or with:

select *
from   quotationsincremental

Additionally, your user no longer needs access to the Exact Online privilege “Download Deleted Records“, which especially helps large accounting firms to speed up their downloads.

Also, the Account column on RevenueByDebtorList, RevenueByDebtorForYear and RevenueByDebtorForYearPeriod no longer displays a 0-value when it should in fact be an empty value.

Changes were made to TimeTransactionsExtended to allow Invantive Control to retrieve the number of hours worked again for projects using the Excel formula I_EOL_TIMESHEETS_AMOUNT.

Teamleader

On Teamleader, a number of new APIs have been added to Invantive SQL:

  • V2.Projects and V2.ProjectById have been extended by approximately 20 budget and actuals fields.
  • The version 2 Teamleader Milestones and MilestoneById have similarly been extended.

Additionally, in addition to the V1 API custom fields, the V2 custom fields have been added using the following new tables:

  • CustomFieldsByCompanyId
  • CustomFieldsByContactId
  • CustomFieldsByDealId
  • CustomFieldsByInvoiceId
  • CustomFieldsByMilestoneId
  • CustomFieldsByProductId
  • CustomFieldsByProjectId
  • CustomFieldsByTaskId

Yuki

On Yuki, Invantive SQL now supports cross-domain queries. The domains are automatically determined from the partitions selected using the use statement.

Availability

All new functionality is solely available on Invantive Cloud and Invantive Bridge Online till the next production release of Windows, Mac and Linux versions.

All bug fixes have been ported to the 20.0 and (where applicable) to 17.32.

2

Hello, I don’t see those TL tables with customs fields from PowerBI oauth.

image
image352×524 74.3 KB

3

Sadly enough Power BI can at the moment not support table functions: tables which are parameterized by a list of parameters. Table functions such as CustomFieldsByCompanyId need a parameter; in this case company ID.

A sample query in which you could use in the SQL editor of Invantive is:

select cfd.type
       cfd_type
,      cfd.label
       cfd_label
,      cfd.group
       cfd_group
,      cfd.required
       cfd_required
,      cpydet.* prefix with 'cfe_'
,      cpy.* prefix with 'cpy_'
from   v2.companies cpy
join   CustomFieldsByCompanyId(cpy.id) cpydet
on     ( cpydet.value_as_string is not null
         or
         cpydet.value_as_number is not null
         or
         cpydet.value_as_number is not null
         or
         cpydet.value_as_array is not null
         or
         cpydet.value_as_boolean is not null
         or
         cpydet.value_object_id is not null
       )
join   CustomFieldDefinitions cfd
on     cfd.id = cpydet.definition_id

For convenience reasons there are a number of views in the schema V1Flat and V2Flat that you can use. At the moment, the V2 custom fields are not in there. For V1 you can find the custom fields.

The Invantive Cloud release is scheduled to contain views for the V2 custom fields by the end of November. Maybe using these views is a viable and timely solution for your task. Otherwise I would recommend using the V1 views with custom fields.