databasejoin where/orderby clause not applied in the list view

[p38]

Using the databasejoin, and using the additional where/orderby clause, I find this is not applied to the list view.

It is correctly applied in the form view, in other words, using

value, section
1,ABC
2,ABC
3,ABC
1,DEF
2,DEF
1,GHI

and {thistable}.section = 'ABC'

will only show the values that contain 'ABC' (i,2,3) in the form view for the dropdown element, but in the list view, this claus is not applied, so consequently one has many duplicates of the same record because the where clause is not applied.

PLEAE NOTE: I am NOT using ID integers in this case, but varchar values.

Please can you correct this.

Paul.

 

[rob]

Hi Paul
The where clause is only there to filter the form field's data, the option is so 'free-form' that it would be impossible to include it into the main list generation query and expect the query to be run successfully.
If you need to filter in the list view a prefilter would be the thing to do. But your comment about the 'duplicates' has me puzzled, which list does the duplicates appear on? Are the duplicates in the list's data, or say in a filter dropdown?
thanks
Rob

 

[p38]

The drop down list displays correctly, in other words the "where" clause works correctly.

In the list view you are doing a left join to display the dbjoin value of the element, but if the dblookup list has 2 values the same, then you will get duplicates on your view list.

This is where the "where" clause will solve this problem. If you applied the where clause as well when you do the left join for the view list, then you wont get any duplicates.

You can easily replicate this by creating a databasejoin table with values that are the same.

Please note that it works correctly when you use lookup Id's that are autoincremented, becuase they are always unique. However, there are many times when you want to use lookup values other than id's, eg: codes for picklists.......

Paul.

 

[p38]

friendly bump please

 

[rob]

You can easily replicate this by creating a databasejoin table with values that are the same.

I always strongly recommend using unique keys, that or you are joining to the wrong table. If two things have the same codes then what in the system/database is differentiating one entry from another?

 

[p38]

If two things have the same codes then what in the system/database is differentiating one entry from another?

The "WHERE" clause....... which is used in the database join.

So my point is, the where clause is applied correctly in the form element lookup list, but it is not applied at all in the list.

if you can apply the where clause in the list then all problems are solved.

If I have a number list like this...

value, section
1,ABC
2,ABC
3,ABC
1,DEF
2,DEF
1,GHI

what is the point of storing id's if I am looking for numeric values?

Furthermore, if I have extensive reporting needed on the database, it means I always have to have a view to provide the actual values.......

 

[rob]

As I said its just not applicable to the list view, without causing massive potential issues to list queries. I still don't get why a table structure :

id, value, section
1-ABC,1,ABC
2-ABC,2,ABC
3-ABC,3,ABC
1-DEF,1,DEF
2-DEF,2,DEF
3-GHI,1,GHI

is not an acceptable solution.

Furthermore, if I have extensive reporting needed on the database, it means I always have to have a view to provide the actual values.......

But aren't you showing (not storing but showing) the section, and not the value?

 

[p38]

Ok, what you are saying is that your list view dbjoin element can only be used if the join element is a unique id as you cannot apply the where/by clause.

What I was trying to explain, is that, for example, you have a picklist of provinces,

1d,code, description
1,WC, (western cape)
2,NC ,(northen cape)
3,LI ,(limpopo)

it makes more sense to store the codes instead of the id, as this then alleviates an extra mySQL statement not so?

Now above example will work in fabrik as each code is unique, so no problems there.....

However, this will not work is you select, eg: "NC"..... you will get 2 rows in your list view.

1d,code, description, country
1,WC, (western cape), south africa
2,NC ,(northen cape), south africa
3,LI ,(limpopo), south africa
4,NC ,(northen cape), namibia
5,LI ,(limpopo), namibia

If one is only interested in the code saved in the master table, it seems a pity we have to create another mySQL statement to display the correct code, as one now stores the id.

In fabrik this is transparent, so not really a problem, but if you are developing major backend systems not using fabrik that makes extensive use of the mySQL tables, esp for reporting, then one always needs to create additional statements to get the code, adding overheads.

Lastly there is one MAJOR drawback of using id's instead of actual values....... if you change your picklist code value, then you lose all historical data, as all views and reports will take on the new code value.



Perhaps one can add this to your tooltip or wiki, where you have the where/orderby statement

IMPORTANT NOTICE - The where/order by clause is NOT applied in the list view

Paul

 

[rob]

I really think you are wrong in your thinking here.

Ok, what you are saying is that your list view dbjoin element can only be used if the join element is a unique id

That's pretty much how relational database work, yes. Any entry in a table needs to have a unique identifier. Not doing so is asking for massive trouble.

Say I store NC, what does that mean? - Does it mean '(northern cape), namibia' or '(northern cape), south africa' your system is ambiguous to say the least.

Lastly there is one MAJOR drawback of using id's instead of actual values....... if you change your picklist code value, then you lose all historical data, as all views and reports will take on the new code value.

Well not all all - if you are consistently storing the ID / UNIQUE reference to the province across the board (which you should be doing), then this ID is immutable and will always be consistent. You can then change the code, the title what ever and your system's data maintains its integrity.

 

[p38]

Nice healthy debate on the go

That's pretty much how relational database work, yes. Any entry in a table needs to have a unique identifier. Not doing so is asking for massive trouble.

Well not all all - if you are consistently storing the ID / UNIQUE reference to the province across the board (which you should be doing), then this ID is immutable and will always be consistent. You can then change the code, the title what ever and your system's data maintains its integrity.

Ahhh, but with esp picklist values, you DO NOT want to change the historical value of what it was in the past, doing it your way this will happen.

So if I stored NC last year, and this year I don't want to use NC in the picklist for future data, but rather WC, if I change NC to WC, then I have lost the integrity of what was captured last year as the view will pull the new code. So if I want to keep what was captured last year, I need to save the record in another table first.

or al;alternatively I need to create new codes each time generating a new id, and if you have a code list of 50 items, it can get pretty cluttered if you only use 5 or 6 each new year.

So one cannot apply relational methodology across the board religiously, it is very much system design driven.

Say I store NC, what does that mean? - Does it mean '(northern cape), namibia' or '(northern cape), south africa' your system is ambiguous to say the least.

Yup, but if we are only interested in the code NC, and used that way, then it is not a problem if "namibia" or "south afica" never co-exist in the same view or query.

Regards

Paul

 

[troester]

To highjack this discussion

or al;alternatively I need to create new codes each time generating a new id, and if you have a code list of 50 items, it can get pretty cluttered if you only use 5 or 6 each new year.

This is how I would do it.
+ an additional field containing "active" or "2014" and than using this field in the dbjoin WHERE.

This way you would only see 5 entries for adding in form view but keep all historical ones in list view.