// Because of how jqxDataTable works: // In a data table with a DropDownList, the selected *display name* associated with the *lookup field name* is returned. // We need to convert this to the non-aliased master table FK ID and lookup the value from the FK table given the name. // ANNOYINGLY, THIS MEANS THAT THE NAME MUST ALSO BE UNIQUE! This should be acceptable, if not even good practice, but it's still an annoying constraint. public void FixupLookups(ViewName viewName, Dictionary <string, object> kvParams) { ViewInfo view = (ViewInfo)db.GetView(viewName.Value); // Find all lookup fields: view.AllFields.Where(f => f.IsFK).ForEach(f => { object val; TableForeignKey tfk = f.References[0]; // assume only one FK involvement for this field. // assume a non-clustered fk. string fkFieldName = tfk.FkPkMap.First().Key; // fk field name string pkFieldName = tfk.FkPkMap.First().Value; // pk field name of referenced table. Assert.That(tfk.ValueFields.Count > 0, "View: " + view.Name + " -> To resolve foreign keys, at least one display field must be defined in the foreign key relationship for " + f.TableFieldInfo.TableInfo.Name + "." + fkFieldName + " -> " + tfk.PkTable + "." + pkFieldName); string aliasedPkValueFieldName = tfk.ValueFields[0]; // Not all FK's in the view may be defined, especially when there are sub-classes of the GenericTableController that // populate the FK values based on session parameters, for example. if (view.Exists(aliasedPkValueFieldName)) { string dealiasedPkValueFieldName = view.GetField(aliasedPkValueFieldName).TableFieldInfo.FieldName; // Aliases can be null for table joins where we're just getting an ID or other fields that don't map to a lookup that needs to be resolved. // TODO: What was this issue really? if (kvParams.TryGetValue(aliasedPkValueFieldName, out val)) { // Remove the *value field* name kvParams.Remove(aliasedPkValueFieldName); // "Please Choose" is text that the jqx dropdown might return, but it seems to return an empty field. if ((val != null) && (!String.IsNullOrEmpty(val.ToString())) && (!val.ToString().ToLower().BeginsWith("please choose"))) { // Replace with the non-aliased *FK field* name. // TODO: This doesn't handle multiple columns referencing the same FK table. // Now, lookup the value in the FK table. Sigh, this requires a database query. If the jqxDataTable / jqxDropDownList worked correctly with ID's, this wouldn't be necessary! // Assume one value field. object id = db.QueryScalar("select ID from " + tfk.PkTable + " where " + dealiasedPkValueFieldName + " = @val", new Dictionary <string, object>() { { "val", val } }); Assert.That(id != null, "Expected to resolve the foreign key ID for primary key table " + tfk.PkTable + " with display value of " + val.ToString().SingleQuote()); // The update will throw an exception if a null FK is not permitted but the lookup returned a null for id. kvParams[fkFieldName] = id; } else { Assert.That(f.TableFieldInfo.IsNullable, "The FK " + fkFieldName + " in the view " + view.Name + " is not marked as nullable. A value is required."); kvParams[fkFieldName] = null; } } } }); }