Answers

• allan Posts: 61,972Questions: 1Answers: 10,160 Site admin

  August 2016

  Are you using the Field->options() method to get the options, or something else? There is a formatter option for the options, which you can use to modify the labels.

  Allan
• ginacargile Posts: 17Questions: 3Answers: 0

  August 2016

  Yes, I'm using field .options. I don't know how to use the formatter to format the values the way I want. ?

  using (var db = new Database(dbtype, conn))
                  {
                      tempInstanceNames d = new tempInstanceNames();
  
                      response = new Editor(db, "tempInstanceNames")
                     .Model<JointempInstanceNamesModel>()
  
                     .Field(new Field("tempInstanceNames.InstanceNameID")
                     .Options("tlkInstanceNames", "ID", "InstanceName")
                     .Validator(Validation.DbValues(new ValidationOpts { Empty = true }))
                     )
                     .Field(new Field("tempInstanceNames.LocationSpaceID")
                         .Options("tlkLocationSpaces", "ID", "LocationSpace")
                         .Validator(Validation.DbValues(new ValidationOpts { Empty = true }))
                     )
                     .Field(new Field("tempInstanceNames.SubLocationSpaceID")
                          .Options("tlkSubLocationSpaces", "ID", "SubLocationSpace")
                         .Validator(Validation.DbValues(new ValidationOpts { Empty = true }))
                      )
                       .Field(new Field("tlkInstanceNames.InstanceName")
                          .Options("tlkInstanceNames", "ID", "InstanceName")
                         .Validator(Validation.DbValues(new ValidationOpts { Empty = true }))
                      )
                     .LeftJoin("tlkLocationSpaces", "tlkLocationSpaces.ID", "=", "tempInstanceNames.LocationSpaceID")
                     .LeftJoin("tlkSubLocationSpaces", "tlkSubLocationSpaces.ID", "=", "tempInstanceNames.SubLocationSpaceID")
                     .LeftJoin("tlkInstanceNames", "tlkInstanceNames.ID", "=", "tempInstanceNames.InstanceNameID")
                     .Where("tempInstanceNames.LocationID", locationID)
                     .Process(request)
                     .Data();
                  }
  
• ginacargile Posts: 17Questions: 3Answers: 0

  August 2016 edited August 2016

  I didn't realize you could do an inline select! I found this link.
  https://datatables.net/forums/discussion/27856/editor-net-field-options-method

  If there is a better way, please let me know.

  Full code here:

  .Field(new Field("tlkInstanceNames.InstanceName")
                          .Options(() => db
                          .Sql("select distinct SUBSTRING(inn.InstanceName,0,PATINDEX('%[0-9]%',inn.InstanceName)) as label, SUBSTRING(inn.InstanceName,0,PATINDEX('%[0-9]%',inn.InstanceName)) as value FROM dbo.tblInstances i LEFT JOIN tlkinstancenames inn ON i.InstanceNameID = inn.ID WHERE SUBSTRING(inn.InstanceName, 0, PATINDEX('%[0-9]%', inn.InstanceName)) IS not null ORDER BY label,value")
                          .FetchAll()
                          )
                      )
  

  Thanks!
• ginacargile Posts: 17Questions: 3Answers: 0

  August 2016 edited August 2016

  Thanks
• allan Posts: 61,972Questions: 1Answers: 10,160 Site admin

  September 2016

  For complex queries that looks about as good as it gets :-)

  Allan