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