ERR500 alter structure: Row size too large.

[XXX-BIT]

Hello @all!

This is my first post here at fabrikar, so please be pacient.

Using:
Joomla 3.1.5
Fabrik 3.1b

Task:
I have to rebuild an existing microsoft CRM solution using Fabrik and Joomla. The idea is, that registered users may fill in some forms and those data will be dumped for transfer to the Microsoft CRM.

I got about 625 fields to create, using the same structure like the MS database.

Problem:
I created a new database table and started to fill in the elements. There are a lot of "string" elements used, where the user has to fill in data manually.
So I banged my head today, receiving the error message:

500 alter structure: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs SQL=ALTER TABLE `fabr_cv` ADD COLUMN `fromos_KrankheitBemerkung` VARCHAR(255) AFTER `fromos_KrankenkasseinD`

I do reckon, that this is a MySQL restriction and I am not actually ready to resize the fields in their length, as well I do not believe it would help me, as I am not even at the half of the element list.

Question:
- How could I create a second / third table and connect those without risking to loose data?
As I see, it is the only way to get around the problem or are there any other ideas?
- How will I be able to dump those different tables in one file for export?
- How do I display different tables on one site (could be in different tabs) after splitting up the main table?


Thanks for your appreciated help and ideas... please do not hesitate telling me, if I forgot do mention something important!

 

[rob]

are there really 625 field all which relate to a single table/object/thing? Is there no way you could normalise those fields into separate tables? Say you have companies, contacts, leads etc each of those should go in its own list/table and you create links between them with joins. Does that sound like something you would be able to implement given your exisitng data structure?

 

[XXX-BIT]

Hy Rob, thanks for your thoughts.

Aktualy this IS the only the Contact form, there are some more, but those I might put in other tables. What I am wondering is HOW to split up this monster... how may I create IDs between tables to identify the corresponding data? You are talking about "Joins"... probably that is what I am looking for. Any more documentation for Fabrik on joins available?

I appreciate your help to get me out this.

 

[rackem]

http://fabrikar.com/forums/index.php?wiki/getting-started/
http://fabrikar.com/forums/index.php?wiki/the-structure-of-fabrik/
http://fabrikar.com/forums/index.php?wiki/design-strategy/
http://fabrikar.com/forums/index.php?wiki/joining-list-and-form-data/

 

[XXX-BIT]

thanks rackem, I thought it would be a boring week-end and you changed it right now...

 

[XXX-BIT]

Ok, I probably got tomatoes on my eyes, but after reading the mentioned links I did not found an answer to my question.

I broke the fields down in different tables, which will ensure that I have the possibility to use more "field" elements. To keep it simple, I have the following tables:

- Contact details
- Extended information

What I would like to do, is to display those two tables on one site. The user should fill in the two parts, this is mandatory. He will start with his contact details and then the extended information is presented. This is, as I do understand a "one to one" connection because every "Contact details" got a corresponding "Extended information" link. Using databasejoin I just find a possibility to let the user actively choose something, which, in this case, isn't what I need.

Could somebody be so kind a lead me to a tutorial how to connect IDs automatically so I might identify the "Contact details" and his corresponding "Extended information" for the same user?

Sorry guys if this is a simple question, but I really can't find anything helping me out!

 

[troester]

From my other post:

An other possibility if you've got really many elements (not necessary if it's 40):
create multiple lists (list1,list2,list3) pointing to your table (will give you list1/form1/group1/elements1, list2/form2/group2/elements2....)
disable all elements you don't need, make sure to have all id elements ENABLED
skip from form1 to form2 by adding a redirect plugin with URL ...form=form2ID&rowid={rowid}...

 

[XXX-BIT]

This thread answered my question for one-to-one relationship! I can go on now, creating different tables and linking them together.

It's easy, if you know how.

Edit: How to set this thread to [SOLVED] ?

 

[rackem]

Glad to hear you figured it out! You can close the thread using the thread tools menu at the top.

 

[troester]

This thread is 4 year old, which part do you like to take?
Related data is ok but joining lists will give you again the full number of elements.

You can close a thread by unchecking "open" in Thread Tools.

 

[XXX-BIT]

troester, as I explained in this thread, I am looking for the best way to split-up a rather big form. There are about 600 elements, which I tried to pull in one table... the outcome was rather bad

I sized it down to less elements, as the microsoft database will take care of some fields when doing the import of the mysql tables. Then I created different tables and I am sorting them in different lists right now.

As all the fields are mandatory, I created a "master" list, called "contact". Here I joined the other tables, using the ID of contact for a one-to-one connection, using a hidden databasejoin field on the other lists, e.g. contact_id. I probably did it "wrong" as I still do not have the overview and the tuts are rather old, not showing the actual version. I am struggling to understand and, as every new-by happy for every bit of information!

Anyway, thanks for your input and I will try to understand how I could resolve this matter in a more elegant way.