Nibble

UI-O-Matic, handling Foreign Keys

Something you’ll bump into sooner or later when working with custom tables are relationships between tables.

Let’s say we have the following 2 pocos (and matching tables)

People

1 [UIOMatic("People", "icon-users", "icon-user",SortColumn="FirstName")] 2 [TableName("People")] 3 public class Person : IUIOMaticModel 4 { 5 public Person() { } 6 7 [UIOMaticIgnoreField] 8 [PrimaryKeyColumn(AutoIncrement = true)] 9 public int Id { get; set; } 10 11 [UIOMaticField("Firstname","Enter your firstname")] 12 public string FirstName { get; set; } 13 14 [UIOMaticField("Lastname", "Enter your lastname")] 15 public string LastName { get; set; } 16 17 [UIOMaticField("Picture", "Please select a picture",View ="file")] 18 public string Picture { get; set; } 19 20 public override string ToString() 21 { 22 return FirstName + " " + LastName; 23 } 24 25 public IEnumerable<Exception> Validate() 26 { 27 var exs = new List<Exception>(); 28 29 if (string.IsNullOrEmpty(FirstName)) 30 exs.Add(new Exception("Please provide a value for first name")); 31 32 if (string.IsNullOrEmpty(LastName)) 33 exs.Add(new Exception("Please provide a value for last name")); 34 35 36 return exs; 37 } 38 }

Dogs

1 [UIOMatic("Dogs", "icon-users", "icon-user", RenderType = UIOMaticRenderType.List)] 2 [TableName("Dogs")] 3 public class Dog : IUIOMaticModel 4 { 5 public Dog() { } 6 7 [UIOMaticIgnoreField] 8 [PrimaryKeyColumn(AutoIncrement = true)] 9 public int Id { get; set; } 10 11 12 public string Name { get; set; } 13 14 [UIOMaticField("Is castrated", "Has the dog been castrated")] 15 public bool IsCastrated { get; set; } 16 17 18 19 public int OwnerId { get; set; } 20 21 public override string ToString() 22 { 23 return Name; 24 } 25 26 public IEnumerable<Exception> Validate() 27 { 28 var exs = new List<Exception>(); 29 30 if (string.IsNullOrEmpty(Name)) 31 exs.Add(new Exception("Please provide a value for name")); 32 33 if (OwnerId == 0) 34 exs.Add(new Exception("Please select an owner")); 35 36 37 return exs; 38 } 39 }

You’ll see that the Dog class has an property of type int called OwnerId, this will be populated with the id of a Person.

If we let UI-O-Matic render this you’ll get an input of type number but that isn’t what we want since we don’t know which number matches a certain person.

Screen Shot 2015-10-26 at 10.57.47

So instead we want the see the persons full name and be able to select it from a dropdown.

All that we’ll need to do is use the UIOMaticField attribute and say that we want to use a dropdown and pass our dropdown some configuration

1 [UIOMaticField("Owner", "Select the owner of the dog", View = "dropdown", 2 Config = "{’typeName’: ‘Example.Models.Person, Example’, ‘valueColumn’: ‘Id’, ’sortColumn’: ‘FirstName’, ‘textTemplate’ : ‘FirstName + \" \"+ LastName ‘}")] 3 public int OwnerId { get; set; }


So the view is set to dropdown and the config we are passing is (in json format)

  • The name of the type that will be used to populate the dropdown
  • The column used for the value of the item
  • The template used for the text of the item
  • The column used for sorting

Now if we let UI-O-Matic render this we’ll get the following result

Screen Shot 2015-10-26 at 11.04.57

Besides dropdown you can also use a checkboxlist or radiobuttonlist, for more info please check the documentation

18 Comments so far

  1. Alex Lindgren on October 26th, 2015

    UI-O-Matic looks really awesome - just waiting for a project where I need it!

  2. Tim Geyssens on October 26th, 2015

    @Alex thanks :)

  3. Matthieu Nelmes on October 27th, 2015

    This just get’s more and more exciting!

    If you could have a list-view within a list-view for instance you have a list-view of customers which you could then click into and see a list-view of dogs/pets! That would be incredible ;)

  4. Tim Geyssens on October 27th, 2015

    @Matthieu yup relationships and the listview is something I still need to look into but could be awesome :)

  5. jonas on December 4th, 2015

    Yeah, that listview within a listview is a really cool feature.

  6. Tim Geyssens on December 4th, 2015

    @Matthieu and Jonas, listview field is now a reality :) check this our post for more details https://our.umbraco.org/projects/developer-tools/ui-o-matic/computer-says-no/73449-listview-within-listview

  7. Robert on December 7th, 2015

    Is there anyway to do 1-to-many relationships? For example, I have a “Representatives” and “Regions” tables. Representatives can have multiple Regions they represent, so I have a middle table to relate the two. With DEWD, I could configure a tab with a multi-select box to edit the relationships. Can this be done with UI-O-Matic?

  8. Tim Geyssens on December 7th, 2015

    @Robert the multi select box isn’t an existing field type yet but you could create it… will try it myself this week

  9. Veronica on February 7th, 2016

    Hi Tim,

    Did you have time to try out the one-to-many relationships mentioned by Robert? I am interested in this too.

    Thanks

  10. Tim Geyssens on February 8th, 2016

    @Veronica not yet sorry (didn’t find the time yet)

  11. Robert on February 17th, 2016

    Oh, sorry, didn’t see your reply here… Still would love this!

  12. Laszlo on March 9th, 2016

    Hi Tim,

    Is there a way to display on the dog listview the owners name instead of the owner’s id?

  13. Tim Geyssens on March 9th, 2016
  14. Tim Geyssens on March 9th, 2016

    SO you’ll need to rebuild the query using the event model, and then add a new property to the class (marked with [ResultColumn])

  15. Aaron on May 11th, 2016

    Hi Tim,

    I’m having the same issue as Laszlo above, and I have followed your example from the links you provided.

    to give you an example in line with your code, my dog listView is not showing the owner name.

    I thought this might be an issue with version (1.6.4) so I updated to 1.7.0, but it still didnt work, and I have a further issue of getting a 404 error when trying to edit or create new records:


    Request error: The URL returned a 404 (not found):
    /umbraco/backoffice/UIOMatic/Object/GetScaffold

    Any ideas?

  16. Aaron on May 11th, 2016

    OK, so I’ve just updated umbraco to 7.4.3 I ahve the latest UIOMatic and the latest UIOMatic T4Templates … and I’m still not getting the foreign key value as per your Dog Listview displaying owner name example

  17. Aaron on May 11th, 2016

    I found the issue… I was adding to the query event BuildingQuery. I changed to BuildedQuery. All working now

  18. marco teodoro on September 5th, 2017

    Hi @Tim Geyssens, i was trying to add a fk collumn name in list view like your sample on dogs model dog owner but so far i can’t make it work. Do you have a sample with the new version?

    i’m using UIOMaticObjectService_BuiltQuery to append my inner join clause and returning the fk col value. i’ve tested the query and is ok on sql server. Also, if i use a list view to display a list of itens i can’t do to edit screen. I always have a checkbox to select the iten instead of the pencil icon to open it. I’m i missing something? do you have a newest sample of how to use list views?

    btw great package.

    Cheers,
    Marco

Leave a Reply