I have to type again the account number creating a PO

[Former Member]

Hi,

using the user fields personnalisation, I'm able to put the account number in the lines during PO creation. The account number is selected by a user table. The account number is existing in the chart of accounts.

When I want to add the PO, the process is stopped, I must type manually the account number (but the same !) in order to finally create the PO.

Any idea ?

[Former Member]

This limitation is from SQL server not B1. Do you have any SP in place too?

[Former Member]

what do you mean by SP ? stored Procedure ?

[Former Member]

Right.

[Former Member]

no, but I can create, is there a solution ?

[Former Member]

No. I mean, if your have SP, the problem would get worse. The problem happen during record creation. Approval procedure and your FMS conflict with each other. Do you use stand Approval or user based?

[Former Member]

My Approval procedure are based on queries filtered on ProjectCode en UDF on the PO.

[Former Member]

You have to fine tune both queries to make them compatible. Use most strict rule to build your query like dbo or other standard naming convention.

[Former Member]

I don't understand where are the incompatibilities. Can you help me ? here is the query used in then approval procedure :

SELECT 'True' Where $[OPOR.U_CodeProjet]='CER001' AND ($[OPOR.DocTotalSy.number]-$[OPOR.VatSumSy.number]<10000)

and the Query used to build the GL Account :

SELECT T0.[U_CodeComptable] FROM [dbo].[@QDX_PROJ_SUBPROJ] T0 WHERE T0.Code=$[OPOR.U_PROJ]

I don't understand why the queries exclude each other and where is the SQL limitation ...

[Former Member]

My recommendation is:

Make another UDF under Marketing Document Title to get FormatCode from UDT to Invoice first. Your FMS should reference to this UDF inside the same form.

[Former Member]

Can you help me to create the query which copy the UDF to put it result in GL account field.

I try something like SELECT $[OPOR.U_test], it isn't ok . U_test is the new UDF that I created to get the FormatCode By query (this one is working)

thanks

[Former Member]

Update system field by FMS will be difficult. What is your GL setup, Segmented or not?

[Former Member]

>

> Update system field by FMS will be difficult. What is your GL setup, Segmented or not?

It seems to be segmented but it is not necessary, if it is easier, I can change it.

[Former Member]

No. That will not be changeable as soon as you posted any transactions. It makes big difference for your FMS.

[Former Member]

What do you advise me ?

In a first way your solutions was to FMS using a query on UDF containing FormatCode. Do you think it's impossible to do ?

[Former Member]

It is not a solution but recommendation only. You are right about the probabilities of the impossible job. If you are using segmented CoA, typing it in will be easier.

[Former Member]

Hi,

Finally I get the solution !

Firstable, in my user table, instead of the FormatCode, I put le AcctCode (_SYS...),

Next, the query used by the GL Account field select the formatCode in OACT attributed to the AcctCode in the UDF.

It seams to work !

Franck

[Former Member]

Now I understand it. The problem is existing in your query. This field you try to update by FMS is actually the Field of FormatCode. You have to give the right value instead of AcctCode.

[Former Member]

do you mean that I have to put AcctCode in my user Table ?

what is FMS ?

Edited by: GEORGES COURONNE on Mar 26, 2009 4:33 PM

[Former Member]

FMS stands for Formatted Search. Your UDF CodeComptable has to be FormatCode instead of Account Number in order to get the actual Account Code.

[Former Member]

I just checked my UserTable and in the 'CodeComptable' column I put FormatCode.

then the query get the formatcode and put it in the GL Account of the line. After add button I've an error. If I put manually the formatCode (the same), there's no error... strange ...

other information, No error if approval procedures are desactivated.

[Former Member]

This is a known limitation when you have active approval procedure in place.

[Former Member]

do you know if it is planned to be updated ?

[Former Member]

Your question is not quite clear. If your have FMS for your UDF, post your query here.

Thanks,

Gordon

[Former Member]

Hi,

I'll try to explain the problem more clearly.

My configuration is :

- a user defined table (QDX_PROJ_SUBPROJ) with 2 columns, ProjectCode, and AccountNumber(CodeComptable).

- on the PO form, I display the User Defined Fields in which there is a field the user enter the projectCode,

- The PO is configured as "Service"

- the GL Account column is filled using a query leaving the Price Column. The Query is : SELECT T0.[U_CodeComptable] FROM [dbo].[@QDX_PROJ_SUBPROJ] T0 WHERE T0.Code=$[OPOR.U_PROJ].

Now, the scenario is :

- I open the PO form,

_ I enter a project code in the UDF,

- I select a Vendor,

- I enter a description,

- I enter the price, I leave the column, the GL Account is automatically filled by the query,

- I click "Add" button

- comments are asked because of workflow,

then I have a error message at the bottom of the general windows : " No matching record found (ODBC -2028) [Message 131-183]

I tried this :

1- before clicking "Add" button, I enter manually the GL account number, exactly the same (filled by the query), and It is OK.

2- other test, I unactivate the approval procedures and it is ok.

I think there is someting with approval procedures, but I am not able the see what ...