cancel
Showing results for 
Search instead for 
Did you mean: 

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

former_member675796
Participant
0 Kudos
990

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

View Entire Topic
Johan_H
Active Contributor
0 Kudos

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
Active Contributor

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
Participant

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
Active Contributor

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
Participant
0 Kudos

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
Active Contributor
0 Kudos

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