0

Select Where Function - Please Help!

Bildschirmfoto 2023-09-22 um 17.43.39

PLEASE HELP!!!

Please see the data model in the attachment below:

 I have a table called Static Site Data, Risks and Mitigation Cost per Site, Type Specific Risks, and Risk List.. 

Static Site Data only shows the details of the site which information needs to be linked to a Report (this report is in a form of another table. The Report will be for each site.)

Risks and Mitigation Cost per site is the big table that is linked to the Static Site Data which calls the names of the sites, showing the type of the site, and the risks.

Type Specific Risks is a table which contains a list of types. Each Type is then linked to the Risk List.

I made a one to many function. A one in the Type Specific Risks and a many in the Risk List... Because 1 type, or 1 row in the Type Specific Risk Table has more than 1 risks, hence making the one to many relationship to the Risk List table

select 'Static Site Data'.'Risks and Mitigation Costs per Site'.'Type Specific Risks'.'Risk List1' where 'Risk List'.Id = 'Column 1'

Why is this code wrong? the function "where" in underlined red by ninox

15 replies

null
    • Fred
    • 7 mths ago
    • Reported - view

    When you do a select command you can only filter at the first table level.

    I'm guessing, from other posts and your picture, that 'Risk List1' is not a table so you can not do a select on a record. You have a table called Risk List so you can use that in your select:

    let t := this;
select 'Risk List' where Id = t.'Column 1'

    There is a way to use reference fields in your filters, but for now let us keep it simple.

      • Randy
      • 7 mths ago
      • Reported - view

       I am having a similar problem trying to create a new Dashboard View.  My understanding of select syntax is:

      select 'Master Table''.'Child Table' where 'Field Name' = conditions

      If I am following your example, let 'Master Table'.'Child Table' := t [a variable]

      select 'Child Table' where "Field Name' = t.'column 1'

      Did I follow your example correctly?  Thanks.

      Screen Shot %22Where%22 Error
      • Fred
      • 7 mths ago
      • Reported - view

      RandyIn a select statement you just use the table name.

      select Table1

      If you want to filter on a field in a related table (could be a child) then you can add it to after the where then you can add the field name:

      select Table1 where referencefieldname.fieldname = data

      But this can be tricky depending if the referencefield is the one side or the many side. We can go over this if you encounter a situation.

      • Randy
      • 7 mths ago
      • Reported - view

       Thanks for your example.  Perhaps I am not understanding what 'referencefieldname'.'fleldname' is in your example

      In my case the 'Field Name' is 'Radio Net Date' and it's in the 'Check Ins' child table of 'Radio Nets' Master parent.  Hence: 'Radio Nets'.'Check ins' where 'Radio Net Date' = conditions ?  That is the error screen shot I included last time.

       In order to create the View window with the data for. my new View, I need the select statement to include both the Master and. and Child, otherwise the window is created, but is empty with no data.

      So how do I make my select statement work adding 'when'?   Thanks, bear with me I'm am brand new here.

      • Randy
      • 7 mths ago
      • Reported - view

       

      Screen Shot Fail Screen Shot Another Fail
      • Fred
      • 7 mths ago
      • Reported - view

      Looking at your pictures and your code, it looks like you want data from Check Ins so that is where you should make your select statements (like in your 2nd pic). All you needed to do was add a closing parenthesis at the end, like the error was telling you.

      In your first picture if you start in Radio Nets then you can't do an equal when you drop into Check Ins because you could have more than 1 Check In record so Ninox will return an array of Radio Net Dates.

      For example, Radio Net record1 has two related Check In records (check1 and check2). So Ninox will find all records in Radio Net and then try to filter them by the Check In relationship. So for record1 it will create an array of data for Radio Net Date, Nov 2 2019, Dec 25,2021. Now you can do an equal cause you have two date data to compare.

      • Randy
      • 7 mths ago
      • Reported - view

       Thank you so much for your further answer!  

      Adding ")" at the end of the Select 'Check Ins' formula did indeed make the error go away as you said.  So then I was able to get this formula to work:

      select 'Check Ins' where 'Radio Net Date' >= xBeg and 'Radio Net Date' <= xEnd   [with no ending  ")" ]

      BUT 'BegDat' Field = Aug 1, 2023 and 'EndDat' Field = Sep 23, 2023.  The 'Check Ins' Table contains 168 records with dates greater than or equal to Aug 1, 2023 and less than or equal to Sep 23, 2023.  Screen shot "Check Ins Table" attached shows some of these records.  I should have gotten an array of the 168 records with this formula, if I am understanding correctly.

      BUT the result is an empty window with no data!  Screen shot "Screen Shot No Data" attached.

      This formula does produce a window with the array of ALL data in 'Check Ins' showing [1077 records]:

      select 'Radio Nets'.'CheckIns'

      whereas, this formula produces a window with an array of only the first 21 records of data showing:

      select 'Check Ins'

      and with the "Radio Net Date' criteria added with "where" - then zero records.

      I have to admit, I'm pretty confused at this point, ugh.  Your answers and attention to my plight as a newbie are most apprecaited!

      Check Ins Table Screen Shot No Data
    • Fred
    • 7 mths ago
    • Reported - view
     said:
    select 'Check Ins' where 'Radio Net Date' >= xBeg and 'Radio Net Date' <= xEnd   [with no ending  ")" ]
    BUT 'BegDat' Field = Aug 1, 2023 and 'EndDat' Field = Sep 23, 2023.

     I'm not sure what you are doing here. What is the BUT part?

     

     said:
    This formula does produce a window with the array of ALL data in 'Check Ins' showing [1077 records]:
    select 'Radio Nets'.'CheckIns'
    whereas, this formula produces a window with an array of only the first 21 records of data showing:
    select 'Check Ins'

     Just to verify that Radio Nets.CheckIns is pointing to the 'Check Ins' table. Unless you changed the name, Ninox will have kept the same name for the reference field as the table. If they are not pointing to the same table then you need to change to the correct table.

      • Randy
      • 7 mths ago
      • Reported - view

       Thanks for your reply.  Trying to clarify, my 'Check Ins" subtable has 1,076 records total.  If I filter for the dates specified in my Dashboard BegDat and EndDat fields, I should then get 168 of these records displayed in my Dashboard.

      The BUT is that I am NOT getting this, but ZERO.   Trying to figure out where/how I am going astray?  Thanks.

      • Fred
      • 7 mths ago
      • Reported - view

      can you upload a sample DB?

      • Randy
      • 7 mths ago
      • Reported - view

       By sample, do you mean an Archive of what I have done so far???  That wold be no problem.

      • Fred
      • 7 mths ago
      • Reported - view

       👍

      • Randy
      • 7 mths ago
      • Reported - view

      Here ya go, uploaded, thanks !

      GMRS Repeaters 4.0.ninox
    • Fred
    • 7 mths ago
    • Reported - view
     said:
    The BUT is that I am NOT getting this, but ZERO.   Trying to figure out where/how I am going astray?

     Ok, this is where having the DB is handy. Your field Radio Net Dates in Check In is not a date field it is a relationship field. Which means that when you select a date, you are actually selecting the record.

    If you create a new formula field in Check In and put this in it:

    debugValueInfo('Radio Net Date')

    It will return: rid(1) <- or whatever number is appropriate.

    To make things easy, create a new formula field in Check In, call it Date, and put this in it:

    'Radio Net Date'.'Radio Net Date'

    Now go back to your Dashboard and modify your view element code to:

    let xBeg := BegDat;
let xEnd := EndDat;
select 'Check Ins' where Date >= xBeg and Date <= xEnd

    And you should get what you want now that you are comparing date data to date data.

      • Randy
      • 7 mths ago
      • Reported - view

       I can't thank you enough!

      I understand your explanation that the 'Radio Net Date' field in Table 'Check Ins' is not a date field, but rather a relationship field, drawing data from the "Radio Nets' Table.  So now I understand why the date fields in the Dashboard were not working with the formula.  Thanks very much for giving me this insight.

      I'm too green to understand the details of the 2 additional formula fields  you gave me, except to understand they essentially convert the relationship info from the 'Radio Nets' Table into date field information. 

      Everything now works as I expected and was trying to accomplish.   If I ever am in a position to return a favor to you somehow, please let me know.  Take care and all the best.  Randy

Login to reply

Content aside

  • 7 mths agoLast active
  • 15Replies
  • 171Views
  • 3 Following