Conflict between two CCD fields in a list/form using same table as source

[molcomp]

Hello,

I have one form/list in which I need to have duplicate elements. I am trying to do the following:

Elements

Project 1 - DD field to table projects
Activities 1- CCD field to relational table intemgroup1 on field acitivity_name, watch Project 1 name on project_name intemgroup1
Subactivity 1 - calc field based on selections from first two fields showing subactivity name from subactivity table

Project 2 - DD field to table projects
Activities 2- CCD field to relational table intemgroup1 on field acitivity_name, watch Project 2 name on project_name intemgroup1
Subactivity 2 - calc field based on selections from first two fields showing subactivity name from subactivity table`

Basically the second group of elements is copy from the first group just with a change on activities2 where watch field is project 2 not project 1.

The problem occurs immediately after creating the second activity 2 ccd field.
I get error 1054 Unknown column 'intemgroup1.activity_name' in 'where clause'.

It looks like the table is locked by the first ccd element and it does not allow access by the second ccd. To debug the issue I have remove the subactivity 2 from the second group to verify that activity 2 is the issue.

Does anyone has any idea how to resolve it?
Alex

 

[troester]

Did you create a new CDD or copy the existing one?
Do you have some custom Where clause?

Multiple DD or CDD joining to the same DB table need to be handled with table aliases, i.e. {thistable} (literally, will be replaced by Fabrik) instead of the table name

 

[molcomp]

Hello troester

Thank you for your help.

I did first try with the copy , since it didnt work I tried creating a new CDD.
In the first cdd I have concat value and there should be in the second one as well (since its replica to the first one) but due to the issue i did not put the concat in the second one.
So basically I have first cdd with concat and i have the second cdd without any where clause.
Regarding the table alias I am not sure how to use it and where since in the cdd element the data and watch field I cannot input just select .

Thank you again.
Alex

 

[lori19]

Hi there

Here's a thought that concerns more your data structure and user experience. I suppose the main thing that the user wants to choose is the subactivity. The "parent" activity is probably more important for the management staff. I had a similar case and solved it like this:

- I put projects, activities and subactivities in one table. It's a bit awkward to enter if your project has 20 activities and each activity 20 subactivities. But usually, you need to do this only once.
- I then have a DD for the project choice and a CDD for the activity->subactivity choice, showing labels like Activity A -> Subactivity A, Activity A -> Subactivity B. The value you can leave on the subactivity, you can always figure out to which activity it belongs, using the projects table.
- Alternatively, it might also possible to use the Activities as <optgroup>, but I only implemented this with radio buttons.

Like this, you can get by with only two fields, a DD and a CDD, which makes your life and especially the life of the user easier.

Kindly,
Lorenz

 

[molcomp]

Hello Lorenz - lori19

Thank you for your help. I managed to resolve the issue using {thistable} instead of table name. I have now this in my concat field for CDD field:
(SELECT activity_name FROM activities WHERE id = {thistable}.activity_name).

I have another problem now with the calc field but let me see if I find a solution .

Thanks again
Alex

 

[beatty_t]

Hey All!

I ran into (what I believe is) a similar issue and it was due to the main form (parent table) and associated tables (via dropdown and cascadingdropdown plugin joins) having elements that were the same name (why troester's {thistable} suggestion helped resolve some issues). Simply renaming the elements (columns) to make sure that there weren't any that had the same name (between all the tables being joined), fixed the issue.

I did some digging and using fabrikdebug figured out that the issue was MySQL related. If the join queries aren't using table aliases, having multiple columns with the same name is "ambiguous".

This is probably enough info for most of you to know what to do from here (if you can rename your columns), but I'm going to explain my scenario and what I did to fix it in a little more detail incase it helps anyone.

My Scenario:
First of all, I had a list called "Licenses" that had several joined tables (Documents, Fee Schedule, Associated Clients etc...). I created a second list called "Licenses ONLY" that only included the Licenses table. It was the "Licenses ONLY" list that wouldn't open, but the issue wasn't related to the joined tables not being joined in the "Licenses ONLY" list, it was the project, building and suite elements that were databasejoin and cascadingdropdown plugins within the "Licenses" table causing the issue. I'm only mentioning this to make sure it is clear which join queries were causing the issue.

In the "Licenses" table (form) a user had to select a "project" from the "projects" table, then a "building" from the "buildings" table and finaly a "suite" from the "suites" table. Within "suites", you have to associate a building and the element name I had used was simply "building". Then, in the "buildings" table, you have to associate a project and the element name I had used for that was "project". Within the "licenses" table, the element names used were project, building and suite (the same table column names used in the other lists/tables). The "Licenses ONLY" form's elements where children to the elements in the "Licenses" form, but that isn't the cause of the issue. The issue is that when the cascadingdropdown plugin for "building" watches the element "project" and has a foreign element that is also called "project" in the "buildings" table, without table aliases to discern which "project" is from which table, MySQL throws out its "column 'some column name' is ambiguous" message.

My Fix:

In each of the associated tables where element names were the same (license, building and suite), I renamed them to include the table name as part of the element name so:

old table.name > new table.table_name

licenses.project > licenses.licenses_project
licences.building > licenses.licenses_building
licences.suite > licenses.licenses_suite
building.project > building.building_project
suite.project > suite.suite_project
suite.building > suite.suite_building