Add Column with "Static" info - like table name

Discussion of issues, suggestions and bugs of Devart Excel Add-ins, our product line for connecting Excel to external data from cloud applications and databases
Post Reply
mckoz
Posts: 2
Joined: Fri 15 Nov 2019 17:18

Add Column with "Static" info - like table name

Post by mckoz » Fri 15 Nov 2019 17:27

Using Quickbooks Addin:

I am trying to add a column to a select statement that contains fixed information so that I can differentiate between two tables in the data. For example, I want the last column to say "Bills" for every entry, but I can't find the correct syntax. Here is an example select statement:

SELECT t.Id,
BillId.TxnDate AS "Trans Date",
BillId.DocNumber AS "Num",
BillId.VendorRefName AS "Vendor",
t.Description AS "Memo",
t.Amount AS "Amount",
t.AccountBasedExpenseLineDetail_AccountRefName AS "Account",
t.LineNum AS "Line #"
FROM BillLineItem AS t
LEFT OUTER JOIN Bill AS BillId ON t.BillId = BillId.Id
WHERE ((BillId.TxnDate BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 00:00:00'))

dm-m
Devart Team
Posts: 3
Joined: Tue 06 Aug 2019 07:43

Re: Add Column with "Static" info - like table name

Post by dm-m » Mon 18 Nov 2019 10:31

Hi,

Thank you for contacting our Devart Team.

We have replied to your email, please check the inbox.

Should you have any questions, do not hesitate to contact us back.

mckoz
Posts: 2
Joined: Fri 15 Nov 2019 17:18

Re: Add Column with "Static" info - like table name

Post by mckoz » Mon 18 Nov 2019 21:08

Figured it out - note the line that is bold:

SELECT t.Id,
BillId.TxnDate AS "Trans Date",
BillId.DocNumber AS "Num",
BillId.VendorRefName AS "Vendor",
t.Description AS "Memo",
t.Amount AS "Amount",
t.AccountBasedExpenseLineDetail_AccountRefName AS "Account",
t.LineNum AS "Line #",
'Bills' AS 'Table'
FROM BillLineItem AS t
LEFT OUTER JOIN Bill AS BillId ON t.BillId = BillId.Id
WHERE ((BillId.TxnDate BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 00:00:00'))

dm-m
Devart Team
Posts: 3
Joined: Tue 06 Aug 2019 07:43

Re: Add Column with "Static" info - like table name

Post by dm-m » Tue 19 Nov 2019 09:04

Dear David,

Thank you for getting back to us.

Sorry for confusion, we did not fully understand your initial question.

Correct, it's possible to specify a condition in SELECT for creating a column to distinguish results between different tables, even if it can't be translated to QuickBooks API. In that case it is executed in cache locally.

Since the Excel Add-ins are using the same providers for cloud applications as dotConnect, you can refer to this knowledgebase article:
https://www.devart.com/dotconnect/quick ... ation.html

Should you have any questions, do not hesitate to contact us back.

Post Reply