CMDBuild Forum

Problem with Filter on REFERENCE field

Old Versions
#1

I am applying the following filter to a REFERENCE field to look up the "Manufacture" from another REFERENCE field "Model".

"Model" is pulled from the "ManufactureModels" table which in turn has a REFERENCE to the "Manufacture" table via the "Manufacturer" field.

from Manufacturer where Id in (/(

select "ManufacturerModels"."Manufacturer" from "ManufacturerModels" 

where "ManufacturerModels"."Id"={client:Model.Id}

and "ManufacturerModels"."Status"='A')/)

 

This works fine for the first edit, updating the Manufacturer field live as you select different Models.

If you then Save or Cancel this edit, all subsequent attempts fail to find any records and simply show the Manufacturer fields previous Id value. If you log out and back in again, again, it works for the first record you edit, but then fails ones you Save or Cancel.

This looks a bit like a bug.

Have I misunderstood something? Is this a client caching issue, or a server side problem?

#2

Hello Chris,

the syntax of your filter seems correct. Could you tell me which version of CMDBuild are you using? 
 
Thank you
CMDBuild Team
 
 
 
 
#3
We are running 2.3.4
 
Previously Tecnoteca wrote:
Hello Chris,
the syntax of your filter seems correct. Could you tell me which version of CMDBuild are you using? 
 
Thank you
CMDBuild Team
 
 
 
 

 

#4

Dear Chirs,

I have not managed to reproduce this problem. I have also noticed that there are some typos in your filter: is the table called ManufactureModels or ManufacturerModels? 
 
For debugging the problem you should take a look at what happens in the browser console. Can you check if the variable Model.Id is resolved and changes its value whenever you select a different value from the drop-down? Or if it get stuck with the older value? See the attached file for an example of what to look for. 
 
Kind regards
CMDBuild Team

 
 
 
 

 


screenshot-from-2016-03-10-08-36-57-png (71.2 KB)
#5
Re table name. I mistyped in my message, the code is correct.
 
From what I can see. The first time you go in, every change to Model does indeed show up in the console that it resolves the Model.Id to a different value.
 
When I cancel/save the card, I get a Java error - it's this that then seems to prevent the code running on the page upon future visits.
I can't attach a screenshot, so I'll describe as best I can...
 
ReferenceField.js
onTemplateResolved: Line 278 call to store.load().
The line below generates a "Unable to get property '0' of undefined or null reference.
this.setValue(records[0].get('Id'));
 
store.load({
scope: this,
callback: function(records, operation, success) {
// Manage preselectIfUnique metadata with CQL filter
if (
!Ext.isEmpty(this.getStore())
&& !Ext.isEmpty(this.attribute)
&& !Ext.isEmpty(this.attribute.meta)
&& this.attribute.meta['system.type.reference.' + CMDBuild.core.proxy.CMProxyConstants.PRESELECT_IF_UNIQUE] === 'true'
&& this.getStore().getCount() == 1
) {
this.setValue(records[0].get('Id'));
}
 
// Fail the validation if the current selection is not in the new filter
me.validate();
afterStoreIsLoaded();
}
});
 
 
Previously Tecnoteca wrote:
Dear Chirs,
I have not managed to reproduce this problem. I have also noticed that there are some typos in your filter: is the table called ManufactureModels or ManufacturerModels? 
 
For debugging the problem you should take a look at what happens in the browser console. Can you check if the variable Model.Id is resolved and changes its value whenever you select a different value from the drop-down? Or if it get stuck with the older value? See the attached file for an example of what to look for. 
 
Kind regards
CMDBuild Team

 
 
 
 

 

 

#6
Further digging back through the call stack, the problem seems to stem from the records parameter being null.
 
The value for records is pulled in from ext-all-debug.js at line 72363 call to operation.getRecords():
 
onProxyLoad: function(operation) {
        var me = this,
            resultSet = operation.getResultSet(),
            records = operation.getRecords(),
            successful = operation.wasSuccessful();
 
        if (resultSet) {
            me.totalCount = resultSet.total;
        }
 
 
Previously Tecnoteca wrote:
Dear Chirs,
I have not managed to reproduce this problem. I have also noticed that there are some typos in your filter: is the table called ManufactureModels or ManufacturerModels? 
 
For debugging the problem you should take a look at what happens in the browser console. Can you check if the variable Model.Id is resolved and changes its value whenever you select a different value from the drop-down? Or if it get stuck with the older value? See the attached file for an example of what to look for. 
 
Kind regards
CMDBuild Team

 
 
 
 

 

 

#7

Hi Chris, 

I have managed to reproduce the problem. Change your filter in this way:
 
from Manufacture where Id in (/(
select "ManufactureModels"."Manufacturer" from "ManufactureModels" 
where "ManufactureModels"."Id"::text='{client:Model.Id}'
and "ManufactureModels"."Status"='A')/)
 
This is needed because the client sends a request with {client:Model.Id} as null when it first loads the form. But this generates an invalid SQL query. With the trick of the cast to text you will avoid the problem. 
 
Hope this helps.
 
Best regards
CMDBuild Team
 
 
 
 
 
 

 

 

1 Like
#8
Thanks.
 
I have found another oddity related to this which looks like a bug. This may have already been addressed, but here is the detail if not...
 
If the Description field of the "Model" you select has a carriage return in it, Model.Id becomes the textual "Description" field contents rather than the numerical Id!
 
Previously Tecnoteca wrote:
Hi Chris, 
I have managed to reproduce the problem. Change your filter in this way:
 
from Manufacture where Id in (/(
select "ManufactureModels"."Manufacturer" from "ManufactureModels" 
where "ManufactureModels"."Id"::text='{client:Model.Id}'
and "ManufactureModels"."Status"='A')/)
 
This is needed because the client sends a request with {client:Model.Id} as null when it first loads the form. But this generates an invalid SQL query. With the trick of the cast to text you will avoid the problem. 
 
Hope this helps.
 
Best regards
CMDBuild Team
 
 
 
 
 
 

 

 

 

#9

Actually the Description field should never contain carriage returns, otherwise you will get errors also without filters.

 
Best regards
 
CMDBuild Team
 

 

 

 

#10
Which begs the question why does the interface allow carriage returns in STRING fields in the first place?
 
Either the interface should not allow CR to enter the field, or it should replace them with SPACE upon save.
 
The Id field should NEVER contain textual content no matter what is in the Description field as it is a INT field. It should only ever contain NULL or INT, not STRING.
 
Is this bug going to be addressed at some point?
 
Previously Tecnoteca wrote:
Actually the Description field should never contain carriage returns, otherwise you will get errors also without filters.
 
Best regards
 
CMDBuild Team