Using SQL query in .NET dynamic code for B1 UP validation

[former_member675796]

Hi Experts!

I have some code that creates a SQL string and then uses it as a query with the 'Recordset.DoQuery' method of the SAP Business One .NET framework.

I have been following an online tutorial. However, I am not able to get the code to run and assign the value correctly.

Also, is it not possible to use a value from the UI in the dynamic .NET code in this way? Do I not have access to those variables?

Any help would be much appreciated. Here is my code:

string query = "SELECT LastPurPrc FROM OITM WHERE ItemCode LIKE $[$4.0.0]";

SAPbobsCOM.Recordset rs = (SAPbobsCOM.Recordset)company.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)

int result = rs.DoQuery(query);

Thanks again,

Alex

[Johan_H · Accepted solution]

Hi ALex,

$[$4.0.0] is a formatted search variable that gets used in the B1 client's sql parser. It only gets populated when a user interacts with the form the formatted search was implemented in.

In your code you are including the variable in the string, which means that it will be sent to DoQuery literally as the string $[$4.0.0].

I strongly suspect that it won't work, but you can try placing it outside the string:

string query = "SELECT LastPurPrc FROM OITM WHERE ItemCode LIKE " + $[$4.0.0];

Next question is, if you want to show a list based on a query, why not just use a formatted search with the given query? Why use code?

Regards,

Johan

[Johan_H · Accepted solution]

Hi Alex,

A formatted search would have to go on the line level, and could look something like this:

SELECT LastPurPrc FROM OITM 
WHERE ItemCode = $[POR1.ItemCode] 
AND (LastPurPrc < ($[POR1.Price.NUMBER] * 0.9) 
  OR LastPurPrc > ($[POR1.Price.NUMBER] * 1.1))

In this example you should get a result only if the price in the purchase order deviates about 10% (up or down) from the last purchase price.

You can test this, and if it works you can adjust it for the desired deviations.

On a separate note, coding opens up a whole world of possibilities, but it is like the proverbial hammer: the more you use it, the more every problem starts looking like a nail. It is not recommended using it when a "smaller" solution, like a formatted search, can do the job.

Regards,

Johan

P.S. Please use the "Add a comment" link below my answer to respond, otherwise I do not get notified.

[former_member675796 · Accepted solution]

Hi Johan,

Thanks for this. Is it possible to use the result of a formatted search inside of a validation?

I would prefer to alert a user at the point at which they're entering a unit price, that the unit price is very different to the last purchase price. Wouldn't a formatted search act a lot like an SAP B1 alert, in that it would find an unusual value, but only after the value had already been entered?

Thanks,

Alex

[Johan_H · Accepted solution]

Hi Alex,

I am not sure what you mean with validation. If you mean B1UP functionality, then I do not know. I have very little experience with B1UP.

This formatted search, when set up correctly, would show the last purchase price in a user defined field (column), that you can move next to the normal price field (column). Trigger it when the price changes. This way, the user is alerted to the fact that they are entering a significantly deviant price at the very moment they enter it. However, this is indeed not a "loud" alert, not a pop-up.

A B1 alert only works after a transaction (purchase order, etc) has been committed. In other words, it is not preventative. It can be handy to alert a user about mistakes that can be corrected.

Another preventative measure is the SBO_SP_TransactionNotification stored procedure, accessible through MS SQL Server Management Studio. Here you can add validations with sql code. This is however an all-or-nothing measure, and will simply block the transaction if certain conditions are (not) met. You can use this method on its own, or in addition to the formatted search. For example, if the deviation is more than say 50%, you could block the transaction, forcing the user to correct the offending price(s). It also allows for a custom message.

Regards,

Johan

[former_member675796 · Accepted solution]

Hi Johan,

Thanks very much for this advice - it's very helpful! Ideally I want to be able to provide a validation like a pop-up with a message. That is why I was trying to use B1UP validations. It seems very limiting that it's not possible to use values from different business objects in validations for these forms though.

Thanks very much for your help!

Alex

[Johan_H · Accepted solution]

Hi Alex,

I just took a look at this video about B1UP validations, and at the bottom of the validations window is a notice of how to use form variables (I think). It looks a lot like the formatted search syntax for variables.

Have a look, and see if this allows for a pop up type warning.

Regards,

Johan

[former_member675796]

Hi Johan,

Yes, I know about the issue with the '$[$4.0.0]' being part of the string. I was just showing my code like that to illustrate what I was trying to do (i.e. use the UI value in the .NET dynamic code).

Yes, I have tried what you suggested (and rightly pointed out probably wouldn't work!) by putting the UI value in the code like " + $[$4.0.0] ". I also tried it like " + $[$4.0.0] + " but yes, it doesn't work. Is there no way to access these values in dynamic .NET code?

What I am trying to do, specifically, is create a validation for when a Purchase Price on an A/P invoice or a purchase order is very different to the Last Purchase Price in the database - and flag this to the user.

I'm using Dynamic Code for this because I thought it was the best method. Also, I'm experimenting with .NET code in SAP.

Thanks,

Alex