Restrict GL lookup to revenue accounts

[pricerc]

Is this correct?

Code: Select all

[ExpSign] = 1 AND [AccClass] = 0 AND [IsEnabled] = 1 AND [PostingAcc] = 1 AND [DistributionAcc] = 0

[pricerc]

also need to add GL_Category.[AccType] = 0

[Mike.Sheen]

If you're wanting to come up with a query for the user to select an account, is there any reason why you wouldn't want the user to select a distribution account? I would expect it to be perfectly reasonable to have a distribution sales account set up to disperse posts to that to other accounts.

It looks like you've arrived at your answer yourself, but here's a query with some comments which might clear things up for anyone else in the distant future stumbling across this (I'm not excluding distribution accounts):

Code: Select all

select GL_Ledger.AccountNo, GL_Ledger.Description, GL_Ledger.ExpSign, GL_Ledger.AccClass, GL_Category.AccType, GL_Category.Description Category
FROM GL_Ledger
JOIN GL_Category ON GL_Category.GLCategoryID = GL_Ledger.GLCategoryID
WHERE GL_Ledger.AccClass = 0 --Normal = 0, RetainedEarnings = 1, Bank = 2, CreditorsControl = 3, DebtorsControl = 4, InventoryControl = 5
AND GL_Ledger.ExpSign = 1 -- Debit = 0, Credit = 1
AND GL_Ledger.IsEnabled = 1 -- Duh
AND GL_Ledger.PostingAcc = 1 -- Duh
AND GL_Category.AccType = 0 -- ProfitAndLoss = 0, BalanceSheet = 1


[pricerc]

If you're wanting to come up with a query for the user to select an account, is there any reason why you wouldn't want the user to select a distribution account?

Not sure. As it happens, this particular customer has no distribution accounts, so it's moot.

That said, I will remove that restriction and allow the customers' accountants to make their own choices

This is for selecting an account for credit card surcharges attached to a sales invoice.

The code will hardly ever be used (probably only once when the plugin is activated), and I probably don't need to filter it at all, but I thought I might as well at least filter out obviously wrong accounts.