Invantive Control is an Excel add-on which allows two-way synchronization between Excel and traditional databases as well as cloud-based applications, using native SQL or Invantive SQL. Include SQL statements and/or block definitions to reproduce the problem, when possible on the reference database Dummy. Also include appropriate tag(s) for the platform you are querying, like exact-online or freshdesk. For product use questions use Super User.
Questions tagged [invantive-control]
32 questions
4
votes
1 answer
Same Exact Online query in Invantive Control for Excel gives different result when executed with customer
I have the following query to make a revenue report based on area groups from Exact Online:
select itemgroupcode
, itemgroupdescription
, code
, description
, country
, postcodegebied
, name
, financialyear…
Bram Reemers
- 99
- 4
3
votes
1 answer
How optimize the performance of SerialNumbers by SalesInvoicesExploded?
I have the following query:
select sie.invoicedate sie_invoicedate
, sie.Silitem sle_item
, sie.Silitemcode sle_itemcode
, sie.Silitemdescription sle_itemdescription
, sie.Silnetprice …
Frank Leemkuil
- 83
- 3
3
votes
2 answers
Error itgensql005 when fetching serial number from Exact Online GoodsDeliveryLines for upload to Freshdesk ticket
I want to exchange information between ExactOnline and Freshdesk based on deliveries (Exact Online Accounts -> Freshdesk Contacts, Exact Online deliveries -> Freshdesk tickets).
The serial number of delivered goods is not available in either the…
H Jansen
- 319
- 1
- 8
2
votes
1 answer
When synchronizing my report with Teamleader I get a itgensql001 error for a custom field
When synching my Teamleader report in Invantive Control I get the following error:
Unknown identifier 'cpyd.c_5_contactpersoon'.
Screenshot of error
Part of the query:
select pjtd.pjt_title pjt_title
, cpyd.c_5_contactpersoon…
Mieke D
- 21
- 2
2
votes
1 answer
itgenclr007: Fix OutOfMemory in 32-bit Excel with VSTO add-in
A complex query using Invantive Control (VSTO add-in with Microsoft .NET 4.7) retrieves approximately 200.000 rows from Exact Online. This works fine in 64-bit versions of Microsoft Excel 2010 and newer.
After a forced .NET garbage collection at the…
Guido Leenders
- 4,232
- 1
- 23
- 43
2
votes
1 answer
How to extract number from a string
I've a string like 'intercompany creditors {DEMO[[1]]}'. I want to extract only the numbers from the string, in example just '1'.
How to do this in Invantive SQL?
H Jansen
- 319
- 1
- 8
2
votes
1 answer
Unkown Index error by using Invantive Control: "The index position 275 is not in use. The last index used is 7"
The error is on the column 'whs.description'. It won't give any error at the join 'ExactOnlineREST.Inventory.Warehouses Whs on Whs.ID=SHO.Warehouse'
select Pro.code
, ShopOrderNumber
, SHO.status
, SHO.Description
, …
Bram Reemers
- 99
- 4
2
votes
1 answer
How to include an Excel formula with a reference to a table in Invantive Control Excel add-in
I'm making a financial report in Excel using the data from Exact Online using the Invantive Control Excel Add-in. In Exact Online I have a classification for the General Ledgers. In my Excel file, I would like to match these classifications on my…
Bram Masselink
- 35
- 3
2
votes
1 answer
Error: itgeneor003 Object metadata could not be retrieved for 'customers1' when creating table
When trying to execute the query below with the Exact Online Adapter in the Excel 2016 for Windows on Windows 10. We get the error Object metadata could not be retrieved for 'customers'. with error code itgeneor003.
create or replace table…
Frank Leemkuil
- 83
- 3
2
votes
1 answer
How to add payment conditions to AR outstanding items in a report on Exact Online?
I am using Invantive Control to create an Excel report with some outstanding invoices information from Exact Online.
I have created a model with the block designer and I have the outstanding invoices information I need. Now I also want to know the…
Jeroen
- 21
- 1
2
votes
1 answer
When syncing my model within Invantive Control for Excel I get the error "Could not find parameter with name 'P_SCHEME_CODE'
When I try to synchronise my model retrieving GL Account Classification information from Exact Online, I get the following error:
itgensdf031: Could not find parameter with name 'P_SCHEME_CODE':
What should I change in order for this to…
Geert Visscher
- 23
- 2
2
votes
1 answer
Validation failed error when inserting/updating Contact into Freshdesk using SQL
I want to update an existing Contact in Freshdesk via API using Invantive Control for Excel Cloud All.
Invantive Control raises a validation error that email address and phone should be unique: Validation failed duplicate_value: email. It should be…
H Jansen
- 319
- 1
- 8
2
votes
1 answer
Error invalid_value on Freshdesk when inserting contact persons from Exact Online
In a Freshdesk testing environment, the following SQL statement copies all contact persons involved with a sales order from Exact Online to Freshdesk:
insert into contacts@freshdesk
( name
, address
, phone
, email
)
select delivery_account_name
, …
H Jansen
- 319
- 1
- 8
2
votes
1 answer
SQL error in Invantive Control for Excel
This is for Invantive Control for Excel.
What could be causing an error at the second CREATE TABLE?
CREATE TABLE niveau1@inmemorystorage
AS
SELECT ID
, Code
, AddressLine1
, Name
, Parent
FROM Accounts
WHERE …
Leo H
- 35
- 5
2
votes
1 answer
Exporting documents from Exact Online for one division gives a spreadsheet instead of separate files
Using the following SQL I get a list of documents and small thumbnails in Exact Online:
select document_account_name
|| document_references_yourref
|| year(document_date) || '-' || month(document_date) || '-' || day(document_date)
…
Joery
- 23
- 3