Is there any date function in query which can be used to insert date and time in SAP B1 automatically?.

[former_member183402]

Hi all,

I have a question, some times I have an idea of to insert datetime in SAP B1 query automatically. So is that possible in SAP B1?

Please anyone can help me.

[kothandaraman_nagarajan · Accepted solution]

Hi,

Please note that, direct UPDATE/INSERT/DELETE sap tables leads to DB inconsistency and may loose SAP support too.

Thanks

[Former Member · Accepted solution]

I think its ok as long as its a UDF/UDT using post transaction notification..

Can you confirm?

I'm not sure..

Thanks,

Joseph

[julie_jamieson2 · Accepted solution]

If you are using Formatted search, just use a SELECT statement to add the value to the field.

To get current datetime stamp would be SELECT GETDATE(). Trigger this on the same field you are using to trigger your other Formatted search.

To update your UDF you should use SAP tools such as DTW not an update query

[former_member183402 · Accepted solution]

Hi Julie,

Let me ask you one more question please,

As it seems it's not allowed to update UDF/UDT by using query. So if yes, how can I use a SELECT statement to add the value to the field without the query?

Lets take an example to PDN1 Table and UDF (U_datetime).

I think DTW updates SAP DB but when you have a template file of data.

Please anyone can help us to solve this question.

[julie_jamieson2 · Accepted solution]

Hi Rurangwa,

You would use a select query and a formatted search on the field.

Create and save the query, then under Tools>Customisation Tools> User defined Values you assign the query to a field, and select a trigger field.

You would need to check the SAP help files for this.

UDF's at line level cannot be updated after add, even DTW will not allow this

[former_member183402 · Accepted solution]

Hi Julie,

Thank you for helping me, but as it seems that the way can be used to one by one Goods Receipt PO document. it cannot help in multiple Goods Receipt PO documents.

Thank you for your good Idea.

[KennedyT21]

Hi

By default SAP save the date and time in the table, what is the use of your date and time...

Rgds

Kennedy

[former_member183402]

Hi Kennedy,

Let me explain in full in order to understand my logic.

Actually I wanted to export xml files of stock (PDN1 Table) by using generic electronic file format (GEP) that was done correctly. But after finishing to export the xml file of all stock the users will add new Goods Receipt PO documents again, from here I will also need to export another file of the new Goods Receipt PO documents, so in order to know exported items and non exported items by my logic I used UDF with N/Y values as it seems to the above screenshot which shows an update query I had to run before adding new Goods Receipt PO. Same time I wanted to update UDFs (U_exported and U_datetime) by using the update query, not allowed.

I read all your replies but I don't find another way which I can use because an update query is not allowed in SAP B1.

According to my logic of to separate exported and non exported Items from stock, is there any other way of to do that without using my way?

Please anyone can help us.

[julie_jamieson2]

You can create a UDT in SAP which you can use an Update query on, after export, update this with the Max docentry you have just exported, then use this to filter the results for your next export.

Another option: You don't need to update PDN1, just OPDN, as the lines cannot be changed after export. A field on OPDN can be updated using the DIAPI

[former_member183402]

Hi Julie,

Thank you so much, I write query of insert and update by using UDT, so it works.

Thanks again.

[a_grootens]

Hi Rurangwa,

Could you be a little more specific and give us an example? It is of course not allowed to insert or alter data directly in the database, but you could use other tools like Formatted Search, ex: to fill the duedate with the Postingdate + 30 days in an invoice you could use:

SELECT CONVERT(DATETIME,$[OINV.DocDate],5)+30

or

SELECT DATEADD(dd, 30, Docdate) from OINV

Let me know what it is you are looking for.

Kind regards,

Andy Grootens

Asecom

[former_member183402]

Hi Andy,

I want to updated user defined field (U_exported) of PDN1 where I will update N to Y, but again I want to update another user defined field of U_datetime in order to know the date or if it's possible datetime of when each Item updated to.

This is the query

.