Hello,
As you can understand from the title, is it possible to create table in the DB and display those fields into opportunity module?
We have so many fields in the opportunity and now when we add more relate fields then the dashlet and export records stops working due to lot of join options in the query.
Is there any other way to achieve it?
Hi @rsp,
that is odd - how many fields are many?
Maybe you can look into DB settings and optimizations, add indexes or similar?
From an architectural point of view, what I like to do to keep modules clean:
If you have a set of 1:1 fields which are conceptually similar, you can just create a 1:1 custom module and store fields there.
For example, if you have an account and KPIs for this account.
Only analysts, controllers or head of roles might need to build reports on the KPIs - but for the day to day marketing and sales, the team members won’t need those KPI values much / often / at all.
Another idea:
Sometimes technical debts have accumulated over time.
There are old fields, not being used anymore and contain old data which is now represented in new fields. Maybe it’s time to tidy up, migrate those field values and delete old fields?
What you’re looking for is probably possible, but it sounds like a bigger customization project, that maybe could be avoided?
After adding fields, they’re around 300-350 fields.
When I added around 25-30 relate fields, then I was getting below error(when user were trying to use the dashlet). We were getting database failed and blank screen when users do mass record export.
ERROR 1116 (HY000): Too many tables; MySQL can only use 61 tables in a join
Yeah, I tried to find it but I did not find solutions for it.
These are good ideas 
Thank you!
Wow!
Out of curiosity: What type of data do you store there?
I’ve had projects with probably 100 - 200 fields in a module.
Often those projects have been a bit messy and one milestone was to tidy up / remove old fields and re-organize the layouts / architecture.
300 - it could be possible but it sounds more like an architectural issue?
If you’ve got many relationships, maybe it makes really more sense to introduce new modules to offload some data / functionality.
I’d usually try to aim for keeping only the fields that are required to be filled by the team members (+ KPI / stats and integration related fields).
As for the KPI: If you grow into a data warehouse / BI company size, you can usually drop them from the CRM.
It has all type of data like text, file, dropdown, multiselect, relate and so on. But, mostly fields are text only.
Yeah, but it is useful have these new fields in the same module for simplicity and to create email and pdf templates.
Yes, most of these fields are filled up by end users.
Are all those part of the same entity? If so, then you shouldn’t be adding separate relate fields, you should just have a single custom table for them (or a couple of tables for a couple of entities) and connect your main record to that one with a relationship (the ones that appear in subpanels).
This is a data design issue (in line with the good advice that @BastianHammer gave you) and I believe you should never allow too may tables or too many fields to clutter your DB. That’s not how SQL and relational DBs are supposed to work - it’s only the rows that should expand. If you detect the borders between your objects/entities correctly, and use proper entity-relationship design, you should be able to keep things neat.
Yes, those relate fields are part of the same entity/module. It is like project tasks that has relate field and user needs to fill up their name in the opportunity module.
Then don’t add 30 relate fields, just make a custom module with 30 fields and create a full relationship to the main module.
Thank you. When you say full relationship, does it mean many-to-many relationship between these two modules?
When you put your fields in a separate table, then it should be one to many relationship with new table. You would be having a select control on new module and a sub panel to select or create under the existing module view pages.
The problem is not your number of fields. I got a module with 380 fields, working fine.
Your problem is the amount of relate fields. You should redesign your relationships. But it is something only you can design because we do not know your structure. Maybe a m:n relationship is suitable for you, maybe not.
Yes, you’re correct! The problem is with number of relate fields in that module.
What is the number of relate fields in that module?
They were more than 61. All are related to users module.
I was getting an error:
ERROR 1116 (HY000): Too many tables; MySQL can only use 61 tables in a join