Question

Stock Count rounding part id

  • 26 February 2024
  • 3 replies
  • 36 views
G
Rank
Badge +1

Hello,

For our stock count we pull the part ID using the below code:

 

var metrixRowId = getValueFromListDataRow(row, "stock_count", "metrix_row_id");
var partId = getDBValue(stringFormat("Select part_id from stock_count where metrix_row_id = {0}", metrixRowId));

 

The problem with this is our Part IDs recently have been rounding off trailing zeroes for example

Part ID 13003.70 becomes 13003.7

1452.00 becomes 1452 

In some cases if 1452 is a valid part, it will pull information from that other part ID.

It doesn’t seem to affect items with a leading zero so

00308.50 is not affected.

We have notes saying that the below code does not work because of an IFS bug, so this was fixed previously but is now broken again.

var partId = getValueFromListDataRow(row, "stock_count", "part_id"); //this returns the wrong value for varchars that end with '.00' due to IFS bug

I have tested the above and confirmed it has the same issue.

 

I am looking for workaround solutions such as converting the result to a varchar or string and back to ensure the trailing zeroes do not get rounded off. 

 

I have also tried wrapping the partID in a convert and it caused every part to break, so there is something here I feel I am not understanding.

var partId = getDBValue(stringFormat("Select CONVERT(varchar, part_id) from stock_count where metrix_row_id = {0}", metrixRowId));

If there is a resource for the syntax for IFS specific SQL statements a link to that would be very helpful.

 

 

Thank you

3 replies

Shneor Cheshin
Rank
Userlevel 6
Badge +26

Hi @gdfkjones 

According to FSM Metadata, STOCK_COUNT.PART_ID is a string. So I am not sure why the script omits characters from the value.

Assuming this is related to FSM Mobile, the syntax you should use is for SQL Lite. 

Query Language Understood by SQLite

So my guess would be

var partId = getDBValue(stringFormat("Select CAST(part_id AS STRING/TEXT) from stock_count where metrix_row_id = {0}", metrixRowId));

 

I am unsure if you should use TEXT or STRING in the cast function. Might be that both will work.

If there is an issue with the function getValueFromListDataRow(), please report and open a support call.

Cheers!

Shneor Cheshin
Rank
Userlevel 6
Badge +26

Hi @gdfkjones 

Another thought.

Create the var first and force it as a string type.

var partId = '';
partId = getDBValue(stringFormat("Select CAST(part_id AS STRING/TEXT) from stock_count where metrix_row_id = {0}", metrixRowId));

Cheers!

Shneor Cheshin
Rank
Userlevel 6
Badge +26

Hi @gdfkjones 

If you liked one of the answers and it helped you resolve the issue, please click on the ‘Best Answer’ button. If you still have questions or doubts, please let us know so we can further assist.

Cheers!

Reply


Terms & ConditionsCookie settings