Prev
Next
Forums User Experience Lawson Add-Ins for Microsoft Office

MS Addins Query GLAMOUNTS amounts in excel as text

 3 Replies
 0 Subscribed to this topic
 1 Subscribed to this forum
Sort:
Author
Messages
Sherry Shimek
Advanced Member Send Private Message
Posts: 43
Advanced Member
3/25/2015 4:08 PM

We are querying the GLMAMOUNTS table using Lawson MS Addins MS Excel is treating all the monthly gl amounts as text.

We have tried setting the format tab to Accounting1-4 and applying to all rows, but this doesn't affect the amounts only the other fields such as the company and other accounting string numbers.

Is there a way in either MS Addins query wizard or excel to default these to numbers.  In large queries, using excel to change convert to numbers takes quite a bit of time.

Thanks,

Sherry Shimek

Catholic Health Initiatives

Sherry Shimek Catholic Health Initiatives Englewood CO
JonA
Veteran Member Send Private Message
Posts: 1163
Veteran Member
4/1/2015 11:01 AM
I don't know why that would happen. I've queried the table with and without the Accounting formatting and all the amounts display as numbers for me. But if you need to convert a column of text to numbers you can use the Text to Columns wizard in Excel. Highlight a column in Excel and go to Data > Text to Columns, Click Finish. Any cell in that column that looks like a number will convert to a number. You can only convert one column at a time.
Jon Athey - Sr. Supply Chain Analyst - Materials Management - MyMichigan Health
Sherry Shimek
Advanced Member Send Private Message
Posts: 43
Advanced Member
4/1/2015 4:35 PM

Thank you.  My guess is that MS Excel formats the amounts fields based on some rule but we cannot find it yet.

We use the Text to Columns but in hundreds of thousands of rows of data an multiple periods of data that takes time we would prefer to conserve. 

Thanks for your response.

 

Sherry Shimek Catholic Health Initiatives Englewood CO
dcaiani
Veteran Member Send Private Message
Posts: 52
Veteran Member
6/12/2015 5:32 PM

I've never experianced numbers coming out as text.

Try Leaving the format at "none".  Then click on "clear worksheet before send"

If you still get numbers coming out as text just highlight the row(s) and at the top of that highlighted row Excel will give you a little error box - use the drop down and click "convert to number"