on 09-30-2008 8:51 PM
I've created a UDF (SalesGL) on the Business Partner master record to enter the Sales GL Account that transactions for this BP should post to. I then wanted to add a formatted search to the GL account on the line level of the Sales Order and AR Invoice to have it look to the SalesGL account associated with a BP and return that GL account. This is the query I've created, but get errors:
SELECT T0.[U_SalesGL] FROM OCRD T0 WHERE T0.CardCode = $[$4.0.0] FOR BROWSE
Any suggestions to correct this?
Auto refresh is checked with When field changes and customer/vendor code selected. Also, I have refresh regularly checked. I've tried with various settings in these fields, but still seem to get the same response.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
The information from Jesper was very helpful. From within the SO, by going to the Query from the Tools menu and executing it I do get the expected result (the same GL as assigned to the BP). However, that # is not populating the GL field on the row, rather the Revenue account from the Item Group is. Further still, if I click on the lookup button in that field, then the correct GL account will drop in. I don't even have to open the lookup, just click the magnifying glass and the account changes! I can type in the account in the format the way the query shows it and it will except that field. Any further suggestions?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I'm still getting an error that reads "...incorrect syntax near '$4.0.0'.2) and Statement 'service contracts' (OCTR) (s) could not be prepared.
Please let me know if there is any other information you need to help with this.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Kathy,
First question, are you using Segmented accounts?
Secondly, there is a way of checking what values the search is retrieving, usually this will help you see what is wrong in the query and what needs to be changed.
1. Open up a Sales Order or an AR Invoice
2. Make the field (GL Account) with the formatted search the active field in the document.
3. Go to Top Menu: Tools -> Queries -> User Queries -> Select the query that has been defined as a formatted search in the active field. The query will execute when you click on it using the current values in the document.
Now:
1. Check the syntax if the query is retrieving the BP defined in the document, yes/no? When the query is run this way the '$'-value should be changed to the BP code. (make sure it is a BP that you have defined a GL account for)
2. Is the query getting a result?
3. If the query is getting a result, is this result a valid value for the field? Meaning, if you take the result and manually put it in the GL Account field, will the system accept it? (example - if the field is numeric and you put in ABC the system will reject it)
Let us know how it goes.
Jesper
Kathy,
If the AccountCode on your BP Master is the actual account number then your SQL should work fine
SELECT T0.U_SalesGL FROM [dbo].[OCRD] T0 WHERE T0.CardCode = $[$4.0.0] FOR BROWSE
If the AccountCode on the BP Master is a _SYS*** number then you need to map the FormatCode from OACT
Let me know
Suda
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
HI Kathy,
What GL Account codes had been saved in your UDF? Account code has the most complicated format to deal with. What you get from screen may not be the same with acctual code.
Thanks,
Gordon
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
89 | |
7 | |
6 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.