FieldtypeJson

[Martijn Geerts]

Please use Matrix Fieldtype & Inputfield

Or the ProFields.

FieldtypeJson (module) Álpha

I've been busy last 2 weeks with a new fieldtype. I needed a fieldtype that generates JSON strings from CSV (excel data). I needed a way to handle a variable amount of columns & needed a way to manage that data. Wanze & Kongondo already worked on a excel crud module with the excellent Excel-like data grid editor Handsontable and I loved that piece of software. Thank you guys. Handsontable is used in the Inputfield part.

What the Inputfield does does:

If there's no data in the field, the Inputfield shows you a textarea. You could copy 'n past in CSV text in that field. When you save the field, all data is processed. Processing means:

• Slice till an x amount of data rows.
• If minimum amount of columns is not reached, pad the columns till the amount you've specified in the settings
• If the data contains to much columns, slice the redundant amount of columns.
• Every data left, is trimmed and sanitized
• Every numbered string will be converted to integers
• Saved in 2 formats: 1) json and 2) cvs

Server side there's a lot of processing needed to perform all this, that is the reason to set a low amount of data rows. On The Inputfield side, the JSON is generated by Handsontable. So this feels a bit dirty. (Server side it does do all the steps in the above list.)

For now it's called FieldtypeJson, but i'm not to keen on this name. 

Output

How to output in your template:

$page->fieldname          // (string) the table markup
$page->fieldname->json    // (string) JSON string
$page->fieldname->csv     // (string) CSV string
$page->fieldname->rows    // (int) number of rows, not really usefull, but needed internally 
$page->fieldname->columns // (int) number of columns, not really usefull, but needed internally

When to use:

• If you need a small set tabular json data, and you don't know how many columns it has.
• If you want to have render tables quicky in your site.

When not to use:

• Store very large sets of query -able data.
• If you have to rely rely on the data. ( all json is stored in a subfield, and the same for csv  )

API Side (JSON):

// Json string
$json = '[["First Name","Last Name","Company Name","Address","City","State","Post","Phone","Email"],["Rebbecca","Didio","Brandt, Jonathan F Esq","171 E 24th St","Leith","TA","7315","03-8174-9123","rebbecca.didio@didio.com.au"],["Stevie","Hallo","Landrum Temporary Services","22222 Acoma St","Proston","QL","4613","07-9997-3366","stevie.hallo@hotmail.com"],["Mariko","Stayer","Inabinet, Macre Esq","534 Schoenborn St #51","Hamel","WA","6215","08-5558-9019","mariko_stayer@hotmail.com"]]';

$page->of(false); // Turn OutputFormatting of
$page->fieldname->json = $json;
$page->save();
$page->of(true);

API Side (CSV):

// CSV string
$csv = '"First Name","Last Name","Company Name",Address,City,State,Post,Phone,Email Rebbecca,Didio,"Brandt, Jonathan F Esq","171 E 24th St",Leith,TA,7315,03-8174-9123,rebbecca.didio@didio.com.au Stevie,Hallo,"Landrum Temporary Services","22222 Acoma St",Proston,QL,4613,07-9997-3366,stevie.hallo@hotmail.com Mariko,Stayer,"Inabinet, Macre Esq","534 Schoenborn St #51",Hamel,WA,6215,08-5558-9019,mariko_stayer@hotmail.com'; 

$page->of(false); // Turn OutputFormatting of
$page->fieldname->csv = $csv;
$page->save();
$page->of(true);

After save, all other subfield are updated. So saving JSON will update CSV and the rows and the columns. And saving CSV will update JSON and the rows and the columns.

Saving direct to the fieldname, or saving to both json and csv or saving to the rows & columns wil thow a wireException().

Download: source on github

Warning: This Fieldtype is Álpha, don't use it in live projects 

empty field:

paste data:

imported data:

manage data:

settings:

Edited November 5, 2015 by Martijn Geerts

[Soma]

Yeah first! Cool, looks great, great idea, haven't been able to look at this yet as just too much going on lately...

[Raymond Geerts]

Nice that you finished it. Looking good, will give it a try.

[arjen]

Very impressive Martijn! Could you tell us more about the use case? I'm really curious what kind of situation you exactly needed this for.

[Martijn Geerts]

Very short anser: charts & tables ( where you don't know the amount of columns )

[Wanze]

This is awesome! Thank you very much

[Wanze]

Idea for features

• Define columns already in the config. The module then displays the handsontable-grid but without data. This way, I can control the data which is entered more precisely.
• Maybe add some more options like:
  • Enable creation of additional columns
  • Enable to insert additional rows
  • ...

These two settings would be very useful for situations where you already know the schema of the data and want to restrict the client from messing up things.

Great module anyway, I just thought to write it down before I forget

Cheers! 

[Martijn Geerts]

First idea = great

Second = already there, right click

On mobile here, will come back on the topic!

[Wanze]

First idea = great

Second = already there, right click

On mobile here, will come back on the topic!

I meant to restrict those possibilities (e.g. as checkbox). If the client should not add new columns, then the item in the context menu in Handsontable should not be active or visible at all. Sorry was not that clear

[Martijn Geerts]

You can limit amount of columns and rows in the settings.
Handson doesn't let you insert or delete more, and if impotered from csv / json, php cut them off.
It's in the fields settings. ( while thinking of it, it might be possible that that is not yet on github, but it's already here on the local install )

Great you're thinking about features.

Define columns already in the config.

Do you mean: Specify real headers in the config, they gonna be used above the dataset. (not part of the actual dataset)

Plus an option to disable add/remove columns at all, so data stays in the right columns ? This all to `force` the client to insert the right data ?

if you have better/more ideas, please shout.

Edited April 4, 2014 by Martijn Geerts

[Wanze]

Sorry I really should try out things first before proposing features that are already implemented

I can accomplish this by setting min/max value of row/column to the same value. Nice!

Great you're thinking about features. What about protecting the first row ? 

This would be great. When setting default columns, the first row (Header) should be protected.

I tried to enter only one row with three values: Column1, Column2, Column3

After saving, I get the three columns. But when choosing "Insert row below", the new row is inserted above. When entering data and hitting enter,

it swaps the rows again but overrides the header value. But works fine with more than two rows

[Martijn Geerts]

Sorry I really should try out things first before proposing features that are already implemented 

I can accomplish this by setting min/max value of row/column to the same value. Nice!

Min / Max, had some fun with that. it doesn't matter witch one is higher or lower . the lowest value is low and the highest is high. Initially I went this ways cause it was easier in the script, after all it doesn't hurt. ( maybe have to add some javascript to catch it )

But when choosing "Insert row below", the new row is inserted above.

The first row is now part of the data set ( lazy mans way ) It will complicate the scripting if the first row would actually be a header. And I don't know it's worth the overhead. But the visual (sticky) behaviour you can put on/off. (Inputfield Setting - First row)

Your idea of setting the headers, would be real headers ( not part of the data set )

[Martijn Geerts]

• Define columns already in the config. The module then displays the handsontable-grid but without data. This way, I can control the data which is entered more precisely.

I really liked this idea & added this setting, tnx for mentioning it @wanze

It's splitted in 2 settings actually.

1. Specify the headers.

2. Start with table.

This way it is possible to start with a table with or without the headers. If start with table is set, it prevents editors to see th CSV import.

As in Europe (most countries) floats are divided with a comma and not divided with a point. As a result CSV data mostly use the semicolon as a delimiter. There's a setting to set the delimiter to comma semicolon or tab. In the Inputfield, the editor can upon CSV import choose which delimiter to use (by default inheriting the default setting).

The force to integer setting, will recognise if the European float, and will convert this to normal float. ( heavy on CPU, so only check if needed ) 

@adrian, also added the ability to delete all data returning to the CSV import.  

[Martijn Geerts]

• Fixed a rendering bug when using this field in FieldsetTabs

Fixed a SQL State bug that could occur in some circumstances when deleting pages using this fieldtype 

[adrian]

Hey Martijn,

Just wanted to say that I finally had a real-world need for this. I combined it in a PageTable template along with X and Y label and chart type fields, together with C3js to create a very easy method for editors to add in dynamic charts anywhere in the page layout with their own data.

Very cool!

Now we just need a CSV import button for ProFields Table and we'll have an alternate solution for letting editors build charts with much larger datasets, but this is still perfect for smaller datasets.

[adrian]

Hey Martijn,

A little typo for you. The module shows up as: Fieldtype Json

https://github.com/Da-Fecto/FieldtypeJson/blob/master/FieldtypeJson.module#L28

[Martijn Geerts]

Thanks, fixed.

[netcarver]

Amazing, how did I miss this!

[Ferdi Derksen]

Hi Martijn,

Maybe you could have a look at the styling when only one row is set, the scrollbars make editing the row very difficult, especially when there is a large amount of columns set.

Tnx in advance and keep up the good work, very pleased with the module!

and again tnx for the headsup last Monday! ;-)

[Martijn Geerts]

To be fair, i've already set this module to be deleted 2 times, but somehow it won't delete.

Better to go for other options like Kongondo's matrix field or use the Profields.

There's big change that there will be Data loss when using this module as it relies heavily upon Javascript.

Edited November 5, 2015 by Martijn Geerts
There are other modules i've set to delete but won''t

[Ferdi Derksen]

Will do - tnx for the info

[Martijn Geerts]

Sorry bout that.