How to use one field for Real and String both value

(0) Share

Report

Posted on by Rahul Saklani

192

Hi All,

We are uploading data through Excel in which we have one field called "Amount". In this field holds different type of value for Example : - there are some lines has real value like "1023.00", "1897.9098", "87554.78" and some values are string like "Included" , "Inserted" , "Added".

Now i have to take these value in SSRS report and same value should appear as it's getting upload.

Now the problem is that

we want 2 decimals value for Real value. if value has more digit after decimal it's taking same value.
if i am taking Number type field in SSRS all string values are gone.
Total also not coming at last if the field is string.

How can we achieve this solution.

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (0)

Sort by

Rati Sharabidze 612 on at

Like (0)

Report
Copy link

Link copied!

Hi Rahul,

You can write a method which will identify real numbers in a string. Identification means that the string only contains numbers and 1 '.' character. About decimal, you should take the substring of your string or round function on converted real value.

Was this reply helpful? Yes No
Rati Sharabidze 612 on at

Like (0)

Report
Copy link

Link copied!

Also,

Validation have to be part of identification meaning that the character '.' must not be the first or last character of the string.

Was this reply helpful? Yes No
Martin Dráb 239,257 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

It's seems that you're getting some rubbish data (text like "Added" isn't an amount), therefore the best solution would be fixing these bugs in data.

If you can't get any more meaningful input, upload data to staging as string and implement business logic in the data entity to identify whether the value is a number or not. Numbers will go to a numeric field; I don't know what you want to do with the rest (e.g. ignoring them or putting them to another string or enum field).

I'm assuming that "uploading data through Excel" means importing data to AX from an Excel file. If you meant something else, please give us a better explanation.

Was this reply helpful? Yes No
Rahul Saklani 192 on at

Like (0)

Report
Copy link

Link copied!

Yes Martin,

Same thing we are doing . We are taking data from excel and uploading it in AX. as i am using a class to upload the data in our tables so there is no staging table in between.

I took a String field in our table and inserted the data in it . But now decimal and Total of all amount is our main concern.

Please suggest .

Was this reply helpful? Yes No
Rahul Saklani 192 on at

Like (0)

Report
Copy link

Link copied!

Thanks Rati,

How can we get the data Type by the Value Mean if value is "Added" then Type = string if "12345.545" then type = real.

Is there any function which could fecth the type by value directly ?

Was this reply helpful? Yes No
Martin Dráb 239,257 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

Oh, so you wrote custom code for the import instead of using the framework that Microsoft built for you... All right, so ignore the part about the staging table and implement the business logic as described above.

Using a string field for storing real values is a bad idea. Use a real field and deal with the non-numeric values in your import logic. Summarize values of the real field will be trivial.

Was this reply helpful? Yes No
Rati Sharabidze 612 on at

Like (0)

Report
Copy link

Link copied!

Hello Rahul

As I know there is no sch function. You need to convert and write each time, however It s better to take strings instead of enum in this case. Better option is to have 2 fields.

Was this reply helpful? Yes No
Suggested answer

Fedir Kryvyi 1,026 on at

Like (0)

Report
Copy link

Link copied!

Okay, there are two possible options.

1. Create "staging table" where your values will be imported from Excel as is + you will have "Status field" that is set to "Not processed by default", and "Error" memo field. After data was imported from Excel to staging table, you can process it with, try to parse real values, do whatever processing you like, and if some unusual case encountered - set Status field to "Error", and populate your error field with details.

2. you can try to parse data from Excel on the go, and in case all values in one line are valid - save this line where you.

Please note - it is a bad idea to have string field that stores real value. It is okay in staging table, because you will not access that field directly, but instead it will be processed by your custom class. Either way, you will need to clean up and parse your data, in first case - after it was uploaded to staging, in second - before porcessing file(or skip invalid lines).

Was this reply helpful? Yes No