0

custom field transformation from SQL

Newbie mistake… I accidentally posted this earlier in the Essentials "How Do I?" forum.

I'm trying to write a custom field transformation to total G/L Entries across several accounts. Yes, we should clean up our Chart of Accounts and use Totaling properly, but this is an interim solution. I have an SQL query that works, but I'm having trouble translating it to the custom field transformation. I found the earlier post that describes essentially removing the SELECT and FROM portions, but that isn't working for me. Here's the working SQL that returns 3 numbers, one for each company:

SELECT SUM([Amount]) AS 'Total Revenue'
FROM [dbo].[NAV_dbo_G/L Entry_R]
WHERE "G/L Account No." like '400101'
OR "G/L Account No." like '400102'
OR "G/L Account No." like '400103'
OR "G/L Account No." like '400104'
OR "G/L Account No." like '400105'
OR "G/L Account No." like '400106'
OR "G/L Account No." like '400107'
OR "G/L Account No." like '400108'
OR "G/L Account No." like '400109'
OR "G/L Account No." like '400110'
OR "G/L Account No." like '400111'
OR "G/L Account No." like '400112'
OR "G/L Account No." like '400113'
OR "G/L Account No." like '400114'
OR "G/L Account No." like '400115'
OR "G/L Account No." like '400116'
OR "G/L Account No." like '400117'
OR "G/L Account No." like '400190'
GROUP BY DW_Account;

So I've put this into the Custom Field Transformation:

SUM([Amount]) AS 'Total Revenue'
WHERE "G/L Account No." like '400101'
OR "G/L Account No." like '400102'
OR "G/L Account No." like '400103'
OR "G/L Account No." like '400104'
OR "G/L Account No." like '400105'
OR "G/L Account No." like '400106'
OR "G/L Account No." like '400107'
OR "G/L Account No." like '400108'
OR "G/L Account No." like '400109'
OR "G/L Account No." like '400110'
OR "G/L Account No." like '400111'
OR "G/L Account No." like '400112'
OR "G/L Account No." like '400113'
OR "G/L Account No." like '400114'
OR "G/L Account No." like '400115'
OR "G/L Account No." like '400116'
OR "G/L Account No." like '400117'
OR "G/L Account No." like '400190'
GROUP BY DW_Account;

Along with variation like braces instead of quotes around [G/L Account No.] and removing the "AS 'Total Revenue' " portion.

We're on NAV 2013 and Jet Enterprise 2014.

The error I receive is on Deployment… just "Failed", "Script" and the contents of "Script" are just four lines of "GO".

2 comments

Please sign in to leave a comment.