Help get this topic noticed by sharing it on Twitter, Facebook, or email.

Assistance please with a SQL Business Rule (VB)

Hi, can someone point me in the right direction hopefully with a bit of vb code. From the documentation I have read so far (lookups etc) I have been unable to get this to work (I don’t want to filter anything based on what’s selected). In short I want to pick my locality name on the create form and the four associated values for that locality name auto populate into the four lookup fields underneath the locality name.
I have a customer table with five lookups on it - Locality name, District name, Sub-district name, local government agency and state.
The Locality lookup has a foreign key to my Localities view.
The localities view also has an id for the district, sub-district, local government agency and state for each locality name. The id’s for these are all foreign keys back to their applicable views e.g. DistrictIDFK in the Localities view is a foreign key back to the ID in the Districts view.
So what I am trying to achieve is have a SQL Business Rule on my customer create form which does the below.
I pick a locality name
The ID for that locality name is captured and used to do a lookup on the Locality view to retrieve the associated district ID for that locality name
The district ID is then used to do a lookup on the district view to get the name of the district.
The district ID and District name are used to set the District lookup field on the create form which I believe requires two values – the ID and the name (string) you want displayed
I then need to repeat this process to set the sub-district name, local government agency name and state name on the create form.
I am also aware of the Context Field and for the district lookup on the create form have set this to the name of the locality name field. Have played with this for a few days and getting nowhere.


These are three of the above lookup fields on my create form

1 person has
this question
+1
Reply
  • Steve

    I am not sure I have a good answer at this point. Perhaps if I understood the process more simply.

    Does each locality have only 1 district, does each district have 1 Region, subdistrict, etc.?

    I ask because it appears only one entry for each is being made in the Master Localities.
    • Hi John
      Sorry for the delay but I have been unwell since I posted this. I will try to explain the process. The locality names in the Localities view are unique of which there are about 1700 of them. Each locality name has one district, one sub-district, one region, one state and one local government agency associated against the locality name. Each locality has a PK. The value for each of the fields I have mentioned are a FK back to the applicable view. Lets take the locality name of Midland in the locality view. The FK for district is 1, the FK for State is 1, the FK for sub-district is 14 and so on. So each column in the locality view represents a number which is the FK back to its applicable view
      PK = 23
      Locality = Midland
      District = 1
      Sub-District = 14
      Region = 1
      State = 1
      Local Government Agency = 45

      So my first drop down list (Locality) gets its data from the localities view. When I select Midland it will record the PK of 23
      After I select Midland I want the remaining lookups on my form for the above values to be auto populated
      I am assuming once I have selected Midland I need a business rule that does something like
      Using the PK of 23 return the FK for District which would be 1
      Set the value for the District lookup to 1
      Next do a lookup on the District view using 1 to obtain the text value of District PK = 1 and display that text value in the District lookup e.g. Perth District
      So the user would see Perth District for district but it will save the PK value of 1
      The business rule would then need to repeat this for each of the other lookups

      The reason they are views and not tables is that I share this data in several applications therefor I keep one master source so if I change a value the new value in the master table it is reflected in every application. Much easier to manage one master set of data than multiples of it in each application.

      Hope that makes sense. I read what Peter said but at this point in time I already have this structure in place. I had it working very easily in my previous software but just trying to get this to work in COT

      Steve
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated happy, confident, thankful, excited

  • Why not create a view (in your database) that consists of all required fields from all required tables, create a new model from that view and use that as your lookup. You can use the Copy facility on the Lookup Page, (in the designer) to allocate all of the desired fields to the new or edited record.
    • Hi Peter
      Thanks for your suggestion. Please see my response to John. All of the required fields are already in the one view (viewLocalitiesMaster). The other fields in this view are all FK's back to their respective view e.g. for the locality name of Midland it has number 1 for DistrictFK which is the PK in the District view for Perth District. Basically I want to select a locality name then fetch all of the FK's associated to that locality name and pass them to their respective lookups on the create form. As they are all lookups I need to save the PK number to the database table but display their text values to the user in the lookup
      Steve
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated happy, confident, thankful, excited

  • Hi Steven. Are the users allowed to click on any of the other Look-ups or just the first one (Locality)?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned

  • Not VB code or even elegant, but a possible solution:

    Your view carries all the required data, so borrow those field from the view when the record is being created, ID included but hidden from the UI.

    Then have a SQL or JS BR (on calculate during execution) that basically copies the ID into the required fields as you require.

    Just make sure that there is another selection or field to complete after this field is populated to change scope and execute the calculate command.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated happy, confident, thankful, excited

  • Steven

    If I was doing this I would look at Peter's solution. You create a db view in the sql db that holds the data you want in the 'lookup'. You can set the necessary relations with 'joins or where' statements.

    Now you have all your data in one spot. You add the view to your models and have controller. You can delete the page it adds or not include it in the menu.

    Then can use it with a look up in COT, filter by the locality field and copy the data to the fields in the form you want to populate.

    Its early here, but this makes some sense to me. It would be a quick solution to what you are doing.

    You can refine it from there if you think it would work.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated happy, confident, thankful, excited

  • *This all assumes the data you want is currently scattered among various tables.

    The db view would contain all the data. In a sense, once run it eliminates all the pk/fk stuff on the COT side. That has been handled on the db side. You just end up with the data you want to use.

    So if I need data from 5 tables. I create a view that has bits of data from the various tables. It gathers it into one place. The view would have a primary key, perhaps the one you use now or the name of the municipality. You could do an auto-lookup for that.

    Once you have the view it can serve as the lookup for your other form.

    Perhaps something like this type of description might help

    5 tables->1 db view->COT controller for view->set lookup to use data from COT view controller->copy data to form or where its needed.

    You can refine this more with filters, etc. so you can narrow the data.

    *This all assume the data you want is scattered among various tables.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated happy, confident, thankful, excited

  • Hi Steven,

    You say that all of the data is available in the view and assuming that the view is what you're using for the lookup, then getting the data into your new record / edited record is straight forward.
    On the Lookup field properties you'll see an option that says Copy:
    In there you can list all of the fields that you want to copy to, from the view.
    The first part is the name of the field in the Form and the second part the name of the field from the lookup. (Although you may see only one field in the lookup all fields in that view are available). It doesn't matter if the Fields have the same name.

    For Example:

    DistictID = Whatever-Its-Called-in-the-view
    SubDistrictID = etc
    etc.

    If that is the case, i.e. all of the data is available in the lookup, then that should work.
    If not then something similar to JJ's idea would work, although personally, I would create a Code Business Rule and write the sql code in there.

    I have a number of apps (in a school funnily enough) where I have Business Rules that retrieve / manipulate data using an SQLAdaptor so no controller is necessary.

    I use c# rather than VB but if you want any code samples let me know. You did say thought that you have done something similar before so if you do need to write a Business Rule the chances are that you have already written most of the code.

    Peter
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated happy, confident, thankful, excited

  • Peter
    Thanks, I will try your example and see how I go.
    The previous software I used was Ironspeed, totally different way of doing it which was through a formula so not applicable for COT.
    Steve
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned

  • I'm also an ex IronSpeeder. I still have it installed as I have a couple of apps that were created using it that I haven't got round to converting with COT yet.
    I actually quite liked IronSpeed as you could basically do whatever you wanted to do with it. Like change foreground and background colours down to individual cells etc. although of course it was using html tables and much older methods than COT. Having said that I've got used to COT and am having fun making it do what I want it to. Most of the time anyway :)

    Peter
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated happy, confident, thankful, excited

  • I’m frustrated
    Hi Peter
    I also liked it as I am not an expert coder as such and it did do a lot of the work for me but as you said I hope to get an understanding of the COT basics as I also enjoy working with COT but its going to take a while. As an example I have been struggling for the last 4 days just to try and get custom membership working. All works until I add a new user and their password doesn't get encrypted. I follow the tutorial but must be missing something
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated happy, confident, thankful, excited