Filament•2y ago

Best way to format data in the table

1. data is stored in DB, for example: 0.40 (as money) and quantity 1234.56 (as float) 2. i would like to display localized data, for example: €0,40 (as money in EUR) and quantity 1 234.56 (as set in selected locale) I made a test for money column: Tables\Columns\TextColumn::make('price')->money('eur') But this displays incorrect value as "€4,00" (it should be €0,40). Q1: What's wrong here? And i see no ->number() type (as in Filament Form component). Q2: What is the best way to do it in Filament Table component? Should i customise every column with my own implementation ->formatStateUsing()?

22 Replies

LeandroFerreira•2y ago

try ->money('eur', true)

GasparOP•2y ago

tried that, the result is €400,00

Patrick Boivin•2y ago

Are you using a package like laravel-money (or MoneyPHP) ?

awcodes•2y ago

I think there's something else going on in your code. Filament uses https://github.com/akaunting/laravel-money under the hood and it's displaying 0,00 for 0.40 for me, so not sure why you're getting 4,00. This is because it's expecting the money to be stored in cents and not fractions of "dollar".

Patrick Boivin•2y ago

I was thinking maybe a double-conversion, like a model attribute cast running after an initial amount conversion. I ran into similar issues when I started storing money as int instead of float...

awcodes•2y ago

this is why we store money as the lowest denomination and convert on output. 🙂

Andrew Wallo•2y ago

Personally, I don't think currency should be stored in the database as float I use decimal

GasparOP•2y ago

I currently use no other packages beside filament for formatting (and it should internally use akaunting/laravel-money). My price format in DB is decimal(9,3) If i display the DB value 0.400 without any formatting, it shows 0.4 by default With money EUR formatting, it shows €400,00 With money USD formatting, it shows $0.40

Tables\Columns\TextColumn::make('price_original')->getStateUsing(function ($record): float {
  return $record->price;
}),
Tables\Columns\TextColumn::make('price')->money('EUR', true),

Tables\Columns\TextColumn::make('price_original')->getStateUsing(function ($record): float {
  return $record->price;
}),
Tables\Columns\TextColumn::make('price')->money('EUR', true),

Andrew Wallo•2y ago

Why are you returning it as a float? This is what I stores amounts in currency as:

$table->decimal('opening_balance', 15, 4)->default(0.0000);

$table->decimal('opening_balance', 15, 4)->default(0.0000);

GasparOP•2y ago

What do you mean by returning as a float?

$table->decimal('price', 9, 3);

$table->decimal('price', 9, 3);

Andrew Wallo•2y ago

You are returning $record->price as shown above as a float

GasparOP•2y ago

i see - this is only for testing.. i changed it to any.

Tables\Columns\TextColumn::make('price_original')->getStateUsing(function ($record) {
  return $record->price;
}),

Tables\Columns\TextColumn::make('price_original')->getStateUsing(function ($record) {
  return $record->price;
}),

Andrew Wallo•2y ago

Well Im just saying floats arent accurate over very small amounts and they could change a sum value over time to be something that isnt accurate

GasparOP•2y ago

Ok. That's why i use decimals. But my problem this time is some weird stuff then i use Filament Table Column with money() for displaying price in EUR. I even tried to export money config and changed some EUR values (thousand separator + decimals to 3) and it did not change anything in my table.

Andrew Wallo•2y ago

Yeah actually Im getting wrong format for Euros as well I just checked

Andrew Wallo•2y ago

Andrew Wallo•2y ago

It seems like since you are getting 400,00 and I am getting 4.000,00 it isn't converting precision correctly, so we may need to format the precision to 2 decimal places before converting it through the money() method... Or I guess we need to change this:

$table->decimal('opening_balance', 15, 4)->default(0.0000);

$table->decimal('opening_balance', 15, 4)->default(0.0000);

to this:

$table->decimal('opening_balance', 15, 2)->default(0.0000);

$table->decimal('opening_balance', 15, 2)->default(0.0000);

Or actually I guess the only way for it to work is to use what akaunting does in their database which is this:

$table->double('opening_balance', 15, 4)->default(0.0000);

$table->double('opening_balance', 15, 4)->default(0.0000);

This converted it correctly for both USD and EUR @gasparsukk Let me know if doing that fixes it for you.

GasparOP•2y ago

This database format change allows nicely format EUR prices. Thank you!

Andrew Wallo•17mo ago

Ive found that the TextInput mask doesnt work for EUR formatting. Doesn't matter if the amount is stored in integer, json, decimal, double, etc... Have you checked in on this as well? Sometimes the TextInput mask, when using something like this, which is accurate for EUR formatting:

->mask(static fn (Forms\Components\TextInput\Mask $mask, Closure $get) => $mask
    ->money()
    ->patternBlocks([
        'money' => static fn (Mask $mask) => $mask
            ->numeric()
            ->decimalPlaces('2')
            ->decimalSeparator(',')
            ->thousandsSeparator('.')
            ->signed()
            ->padFractionalZeros()
            ->normalizeZeros(false),
])

->mask(static fn (Forms\Components\TextInput\Mask $mask, Closure $get) => $mask
    ->money()
    ->patternBlocks([
        'money' => static fn (Mask $mask) => $mask
            ->numeric()
            ->decimalPlaces('2')
            ->decimalSeparator(',')
            ->thousandsSeparator('.')
            ->signed()
            ->padFractionalZeros()
            ->normalizeZeros(false),
])

Changes a value like "24.45" in the database to "2.445,00".

GasparOP•17mo ago

I also could not use masks - they do not work with formatting.

Iliyas M•17mo ago

Have u tried this?

Tables\Columns\TextColumn::make('price')->money('eur', 100)

Tables\Columns\TextColumn::make('price')->money('eur', 100)

Andrew Wallo•17mo ago

TextColumn money format works fine just not TextInput masks

Gaming

Programming

Best way to format data in the table