5.95 dataformat for decimals (german) does not work

Please report bugs and any annoyances here. Kindly include all possible details: steps to reproduce, expected result, actual result, screenshots, ... etc.
Post Reply
User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

5.95 dataformat for decimals (german) does not work

Post by onoehring » 2021-04-20 13:50

Hi,

I have an application for the german language where numbers are written like this: 1.234,56 where "." is the thousands seperator and "," the decimal seperator. The number would equal 1234.34.
I have defined the field as decimal (10,2) and set the dataformat to (c:\Program Files (x86)\AppGini\add-ons\dataFormats.cfg)

Code: Select all

FORMAT(%%FIELD%%, 2, 'de_DE')
The data is displayed correct in table and detailview (when I entered data like 1234.56 directly in the database before) - but I am unable to enter the new data in the detailview. AG does not transform the entered data into the data expected by the database.
The error is like this:

Code: Select all

Incorrect decimal value: '28,25' for column 'einzelpreis' at row 1
The field in the insert query held the value `einzelpreis`='28,25',

I think this is a bug, as the user will try to input data as he is used to. German users would try to enter "1234,56" and the attempt to safe will fail.

Olaf

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: 5.95 dataformat for decimals (german) does not work

Post by pbottcher » 2021-04-20 19:46

Hi Olaf,

I think that at this point AppGinit only converts the data from the database towards your definition for the output, not vice versa.
If you want to enable the user a german number you need to convert that number in the hook file to the correct db correspondant.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: 5.95 dataformat for decimals (german) does not work

Post by onoehring » 2021-04-21 06:42

Hi pbötcher,

thank you for your reply. I understand - but I really think the current behavior is misleading for the user - and the developer.
Your suggestion to transform the value in the hooks is good anyways. Jan suggested (by mail) some other solution. When I tested his solution, I will report back.

Olaf

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: 5.95 dataformat for decimals (german) does not work

Post by onoehring » 2021-04-21 07:43

Hi pbötcher,

your suggestion does work .. in a way.
When I add the following code to the _before_insert and _before_update hook functions the value is saved in the format the db expects: instead of 123,45 the value of 123.45 is recorded in the database.

Code: Select all

function ..._before_update(){
  $data('price']) = db_price($data('price']))
}

function ..._before_insert(){
  $data('price']) = db_price($data('price']))
}

// extra function
function db_price($price){
	$price= str_replace(",", ".", $price);
	$price= str_replace(" ", "", $price);
	return $price;
}
BUT: Immediately after saving in the display of the detailview the value is also displayed as "123.45" (and not according to the settings as 123,45).
When I switch back to tableview the value shoes up as 123,45 (with comma as decimal separator). Clicking into the details of the record the value also shows 123,45.

Olaf

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: 5.95 dataformat for decimals (german) does not work

Post by pbottcher » 2021-04-21 20:18

Hi Olaf,

agree.

This should be handled in both (tableview and detailview). Unfortunately it is not. DV is taking the plain data from the database.

As a interims solution you could edit the templates/TABLENAME_templateDV.html file.

Look for the value="<%%VALUE(YOURFIELDNAME)%%>"

and change the type="text" to type="number" and add lang="de-DE"

With this setting you do not need to change the data in the before_insert / before_update hooks.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: 5.95 dataformat for decimals (german) does not work

Post by onoehring » 2021-04-22 12:05

Hi pbötcher,

thank you for your suggestion. Sounds good as an intermediate solution (until AG handles this correct).

Olaf

AhmedBR
AppGini Super Hero
AppGini Super Hero
Posts: 327
Joined: 2013-09-19 10:23

Re: 5.95 dataformat for decimals (german) does not work

Post by AhmedBR » 2022-07-29 21:59

pböttcher wrote:
2021-04-21 20:18
s should be handled in both (tableview and detailview). Unfortunately it is not. DV is taking the plain data from the database.

As a interims solution you could edit the templates/TABLENAME_templateDV.html file.

Look for the value="<%%VALUE(YOURFIELDNAME)%%>"

and change the type="text" to type="number" and add lang="de-DE"
Hi there,

Works if you do not need to edit this value.
But if you do, it restricts the editing to around 1 integer, and cannot change the decimals, at least this is what is happening with me v22.14
I am still looking for a good solution for this one. :?
AppGini 22.14 - xampp 3.3.0 - PHP 7.4.30 - Summary reports - Calendar - Mass update - Messages - AppGiniHelper

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: 5.95 dataformat for decimals (german) does not work

Post by pbottcher » 2022-07-30 08:12

Hi,

add step="0.01"

so change

type="text" to type="number" and add lang="de-DE" and step="0.01"
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

AhmedBR
AppGini Super Hero
AppGini Super Hero
Posts: 327
Joined: 2013-09-19 10:23

Re: 5.95 dataformat for decimals (german) does not work

Post by AhmedBR » 2022-07-30 10:38

Thanks for the reply it works :D

For some reason that did not work about two weeks ago, was a test app, does not exist anymore to recheck it.

I was able to change the decimal and the integer, but noway it would allow me to save anything out of just 1 integer around the number that was already entered, unless I use point instead of comma.
Like: I had 554,50 it would only allow me to change from 553,50 and up to 555,50, always 0,50 unchanged and cannot be below 553,50 or above 555,50!
Why I am mentioning this story: If it works, then there is a conflict or something missing some where that you need to find!

Still I have one more problem:
If you have a look up value for balance, it would still show with POINT, since there is no type to change for the field

Code: Select all

				<div class="form-group payment-balance">
					<hr class="hidden-md hidden-lg">
					<label class="control-label col-lg-3">Balance</label>
					<div class="col-lg-9">
						<div class="form-control-static" id="balance"></div>
					</div>
				</div>
Unless I find something to solve this, this is what I have in mind:
Add a column to the main table save the balance as VarChar with comma and get this in the look up (not used in calculations, just show the balance as autofill) and should show with comma.

Thanks pböttcher for making me try it again :D
AppGini 22.14 - xampp 3.3.0 - PHP 7.4.30 - Summary reports - Calendar - Mass update - Messages - AppGiniHelper

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: 5.95 dataformat for decimals (german) does not work

Post by pbottcher » 2022-07-30 16:39

Hi,

if you have a lookup on the decimal value you need to change the lookup sql (go to the advance settings and unselect show all records.

Then put the lookup field in the FORMAT syntax

FORMAT(`TABLENAME`.`FIELDNAME`, 2, 'de_DE')

This shall display the german values in the dropdown.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

AhmedBR
AppGini Super Hero
AppGini Super Hero
Posts: 327
Joined: 2013-09-19 10:23

Re: 5.95 dataformat for decimals (german) does not work

Post by AhmedBR » 2022-07-30 17:10

Thanks, that is a good idea.

By the way, while I was editing the template I got an idea that worked very well.
As I try to stay away as much as possible from changing anything that can be overwritten.

You can change the type via JavaScript instead of the template, running smooth, I am at the testing Beta stage and all seems good so far!
Give it a shot :D
AppGini 22.14 - xampp 3.3.0 - PHP 7.4.30 - Summary reports - Calendar - Mass update - Messages - AppGiniHelper

AhmedBR
AppGini Super Hero
AppGini Super Hero
Posts: 327
Joined: 2013-09-19 10:23

Re: 5.95 dataformat for decimals (german) does not work

Post by AhmedBR » 2022-08-01 15:59

Hi,
Here is the java I am using in case someone needs it:

Code: Select all

document.getElementById("value").type="number";
document.getElementById("value").lang="de-DE";
document.getElementById("value").step="0.001";
Still one problem to go, if you have a value with no decimals, it appears as POINT
All three fields are exactly same settings, 20 will still appear as 20.000 instead of 20,000
Attachments
number.png
number.png (2.08 KiB) Viewed 2908 times
AppGini 22.14 - xampp 3.3.0 - PHP 7.4.30 - Summary reports - Calendar - Mass update - Messages - AppGiniHelper

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: 5.95 dataformat for decimals (german) does not work

Post by pbottcher » 2022-08-01 19:42

Hi,

can you double check that. Is the value really 20 in your database? It should be 20.000. Also I would be surprised to see that not being converted.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

AhmedBR
AppGini Super Hero
AppGini Super Hero
Posts: 327
Joined: 2013-09-19 10:23

Re: 5.95 dataformat for decimals (german) does not work

Post by AhmedBR » 2022-08-01 20:41

Hi,

I am writing just 20 in the field, and field is decimal(10,3).
tanque.png
tanque.png (5.92 KiB) Viewed 2885 times
The value in db is 150.582 20.000 10.562 and here are the values in db
db.png
db.png (5.91 KiB) Viewed 2885 times
and here is js I am using, are the same for three:

Code: Select all

document.getElementById("tanque1").type="number";
document.getElementById("tanque1").lang="de-DE";
document.getElementById("tanque1").step="0.001";

document.getElementById("tanque2").type="number";
document.getElementById("tanque2").lang="de-DE";
document.getElementById("tanque2").step="0.001";

document.getElementById("tanque3").type="number";
document.getElementById("tanque3").lang="de-DE";
document.getElementById("tanque3").step="0.001";
What am I missing, or not seeing?
AppGini 22.14 - xampp 3.3.0 - PHP 7.4.30 - Summary reports - Calendar - Mass update - Messages - AppGiniHelper

AhmedBR
AppGini Super Hero
AppGini Super Hero
Posts: 327
Joined: 2013-09-19 10:23

Re: 5.95 dataformat for decimals (german) does not work

Post by AhmedBR » 2022-08-04 00:38

Did any one try this mask as currency with coma instead of point as decimal separator?
https://github.com/RobinHerbots/Inputmask

It is one of the best masks out there, I do recommend it for everything except currency for now.

Everything in this mask works perfect except currency with coma!
I am using it for phones, zip codes, number plates, you name it, EXCEPT currency.

I even tried a new app with just two fields table, still does not work!
After you save, it adds two zeros, you save again, you loose two zeros from the integer, it is crazy, tried all the examples mentioned, nothing is working.
I am sending it to Ahmed to take a look, because I believe there is some conflict with something in Appgini.
AppGini 22.14 - xampp 3.3.0 - PHP 7.4.30 - Summary reports - Calendar - Mass update - Messages - AppGiniHelper

AhmedBR
AppGini Super Hero
AppGini Super Hero
Posts: 327
Joined: 2013-09-19 10:23

Re: 5.95 dataformat for decimals (german) does not work

Post by AhmedBR » 2022-08-05 10:51

Here is the number entered:
1.png
1.png (5.33 KiB) Viewed 2786 times
After saving
2.png
2.png (5.78 KiB) Viewed 2786 times
Next save:
3.png
3.png (5.19 KiB) Viewed 2786 times
So it keeps adding or removing 2 zeros on every save :lol:
The above tested on fresh app!
AppGini 22.14 - xampp 3.3.0 - PHP 7.4.30 - Summary reports - Calendar - Mass update - Messages - AppGiniHelper

AhmedBR
AppGini Super Hero
AppGini Super Hero
Posts: 327
Joined: 2013-09-19 10:23

Re: 5.95 dataformat for decimals (german) does not work

Post by AhmedBR » 2022-08-05 10:53

This is the mask I am using that is causing this issue, maybe something is wrong in it:

Code: Select all

$j("#valor").inputmask('currency', { alias: "numeric", rightAlign: false,  groupSeparator: ".",	radixPoint: ","});
also tested with alias currency same result.
AppGini 22.14 - xampp 3.3.0 - PHP 7.4.30 - Summary reports - Calendar - Mass update - Messages - AppGiniHelper

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: 5.95 dataformat for decimals (german) does not work

Post by pbottcher » 2022-08-05 18:41

Hi,

just out of curiosity,

Did you test with the "only" setting the input field to numeric without additional inputmask?
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

AhmedBR
AppGini Super Hero
AppGini Super Hero
Posts: 327
Joined: 2013-09-19 10:23

Re: 5.95 dataformat for decimals (german) does not work

Post by AhmedBR » 2022-08-05 22:53

Hi pböttcher ,

Yes, I did, works normal.
Actually everything in the mask works perfect as I am using it for many fields, expect currency, currency behaves crazy!

Like, if it was not working I would say corrupt file, bad installation etc.

Here is a tiny app that I made just for testing with two fields telephone(works perfect) and currency (the crazy one)
https://ufile.io/ucjxz5mq

If you got some time give it a try.

Does the currency mask depend on anther file that is missing (just a thought)?
Because everything works expect currency!
Could be something simple but I am just not seeing it, or something is messed up on the server, but it behaves the same on localhost!


Thanks
AppGini 22.14 - xampp 3.3.0 - PHP 7.4.30 - Summary reports - Calendar - Mass update - Messages - AppGiniHelper

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: 5.95 dataformat for decimals (german) does not work

Post by pbottcher » 2022-08-06 20:08

Hi,

as the name already says, it is a mask change, not a numeric transformation.
So in order to use that library you may want to translate the inputed value in the before_inser and before_update hook to the "dotted" numeric value.
To display the value correctly you could change your SQL query in the _init hook to retrieve the data with "," instead of ".".

Otherwise the library will transform your data wrong.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

AhmedBR
AppGini Super Hero
AppGini Super Hero
Posts: 327
Joined: 2013-09-19 10:23

Re: 5.95 dataformat for decimals (german) does not work

Post by AhmedBR » 2022-08-07 18:27

Thanks for the reply.

I have done it another way, easy for me to implement and it works, I am sharing here in case some one needs it:
created a VarChar to get the masked value, this will save and retrieve without any changes.
I manipulate that in the befores hooks for the field I am using for calculations behind the scene.

Code: Select all

	$data['valor'] = str_replace(".","",$data['valor2']);
	$data['valor'] = str_replace(",",".",$data['valor']);
I heard there is a better way, but not sure how that works yet, here it is if someone wants to play with it, there is onBeforeMask: function

Code: Select all

$('.money').inputmask('decimal', {
      radixPoint:",",
      groupSeparator: ".",
      autoGroup: true,
      digits: 2,
      digitsOptional: false,
      placeholder: '0',
      rightAlign: false,
      onBeforeMask: function (value, opts) {
        return value;
      }
});
By the way this also resolves my problem of the 20.000, it will now show 20,000
Thanks
AppGini 22.14 - xampp 3.3.0 - PHP 7.4.30 - Summary reports - Calendar - Mass update - Messages - AppGiniHelper

Post Reply