cancel
Showing results for 
Search instead for 
Did you mean: 

Formatted search for GL account #

Former Member
0 Kudos

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?

Accepted Solutions (0)

Answers (5)

Answers (5)

Former Member
0 Kudos

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.

former_member583013
Active Contributor
0 Kudos

The row level FMS should have Auto Refresh on a row level field. I would suggest you change the Auto Refresh on ItemCode

When you actually select the BP on a new marketing document, the rows do not exists and therefore there is not Auto Refresh that is fired.

Suda

Former Member
0 Kudos

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?

former_member583013
Active Contributor
0 Kudos

Have you set the formatted search to Auto Refresh...........Refresh Regularly or Display Saved Values?

Former Member
0 Kudos

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.

Former Member
0 Kudos

be sure, you don't execute the query from the generator. it is not possible. you may try it as a formated search in the correct field.

JesperB1
Advisor
Advisor
0 Kudos

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

former_member583013
Active Contributor
0 Kudos

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

former_member583013
Active Contributor
0 Kudos

Kathy

If it is a Service type AR Invoice, probably you need to use

IF $[$3.0.0] = 'S'
       SELECT T0.U_SalesGL FROM [dbo].[OCRD] T0 WHERE T0.CardCode = $[$4.0.0]

Former Member
0 Kudos

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