Can I lookup for a value in a table if an specific column isn't in the db schema but it's on the table?
Hi,
I have a table with several columns (In SQL Server), but the db schema has not all columns. Can I lookup from my code using a column that isn't on the db schema, but exist on the table?
This is an extracto of the code, I tried, but having data, always returns false (doesn't found the record I'm looking for)
if (codigoJI !== null ) {
var select = Ext.create( "Terrasoft.EntitySchemaQuery", {
rootSchemaName: "UsrJardInfil"
} );
// Columna(s) a evaluar
select.addMacrosColumn( Terrasoft.QueryMacrosType.PRIMARY_COLUMN, "Id" );
// Lista de campos a recoger
select.addColumn( "Name" );
select.addColumn( "Code" ); // This column is at the table in SQL Server, but it isn't at the db schema
// Condicion WHERE
var filters = Ext.create( "Terrasoft.FilterGroup" );
filters.addItem( select.createColumnFilterWithParameter( Terrasoft.ComparisonType.EQUAL, "Code", codigoJI ) );
select.filters = filters;
// Ejecuta consulta
select.execute( function( responseSelect ) {
MaskHelper.HideBodyMask();
if ( responseSelect.success ) {
if ( responseSelect.collection.getCount() > 0 ) {
// Asigna los valores a los campos de Niño
var jardinInfantilNombre = responseSelect.collection.getByIndex(0).get( "Name" ) ? responseSelect.collection.getByIndex(0).get( "Name" ) : null;
var jardinInfantilId = responseSelect.collection.getByIndex(0).get( "Id" ) ? responseSelect.collection.getByIndex(0).get( "Id" ) : null;
this.set( "UsrJardinInfantil1", {
value: jardinInfantilId,
displayValue: jardinInfantilNombre
} );
} else {
this.set( "UsrJardinInfantil1", null );
}
}
}, this );
} else {
this.set( "UsrJardinInfantil1", null );
}
},
I attach also an image of the table schema and the table structure on SQL Server
Thanks in advance
Like
You will not be able to operate those columns from the interface. You need to add the columns to the object if you want to see them in the EntitySchemaQuery result.
If you want to see the columns in the EntitySchemaQuery result but you don't want to allow users to see them in filters and grids, try to setup usage mode Expanded for them.
Thanks Mark,
We understood that the "usage mode" option we must use to this was Unlimited and we did so in those fields, now those fields are not shown in the schema, how can we revert the usage mode to those fields if we can not see them?
Thanks in advance,
Thanks Mark,
It doesn't works, Wich could be the reason? perhaps the columns where added mannually on the table on SQL Server after the entity was created?
How can I saw thos fields on the schema?
Thanks in advance
>perhaps the columns where added mannually on the table on SQL Server after the entity was created?
This way just create the columns with the same names and types in the schema. Please note that if you see FieldNameId in the database, the name in the object will be without Id. Just FieldName. And the type of the field will be lookup.
Thanks Mark, I tried,
One of the field name in db (SQL) is Code type nchar(3)
I try to create the field in the schema, but cannot because get a message error requiring the field must start with the Usr prefix: Error while saving: The "Code" name of the "CodigoNivel" column must start with the "Usr" prefix
How can I add the field?, it's not too much important now, becasue I created a new filed in the schema and update the db with tha value of this field with the another filed, but in another table we have more fields we need to have available in the schema.
thanks again!
You can find the prefix in the system setting "Prefix for object name". Clean the value and name the columns as you want.
Thanks Mark
Get an "Error while saving: Type "NCHAR" is not supported." because one of the fields on the table is nchar(10), what are the options?, modify the table field types? other?
thanks
I would create a new field, backup the data and convert the type in the database to NVARCHAR(250). Then I would transfer the data from the backup column and droped it.