3

Pass current viewing record Id to a view element column formula

Hi all -

I have a table that has a view element. I'm trying to do formulas in the view element that requires data from the record I'm viewing. As we know, the view element takes you to another table so I've lost any connection to the record I'm viewing. I can do

let x := this;

 

when I'm doing the formula for the view element, but after that when I add a formula column in the view element I've lost any connection to the current viewing record. So if I did the above in the formula for the view element column, the "this" is now the table that is referenced in the view element and no longer the record of the table that I am viewing.

Has anyone figured out a way to do this?

13 replies

null
    • Alain_Fontaine
    • 2 yrs ago
    • Reported - view

    I also encountered situations where such an access would have been needed, and I have been unable to find a solution. If there is one, I would love to learn. If there is none, well, maybe it would be time to ask Ninox for one. I mean a keyword that would represent a handle to the current view record.

    • John_Halls
    • 2 yrs ago
    • Reported - view

    Hi Fred

    I spent an evening trying to find a solution to this and, as Alain and yourself have concluded, there is no solution. All I could suggest is to run a script from a button in one of two ways

    1. Set a flag on the current record which the formula in the view can pick up in a select statement.
    2. Loop through the view data and add a link to the current record.

    Both have their pluses and minuses.

    Regards John

      • Fred
      • 2 yrs ago
      • Reported - view

      John Halls Yeah, those were my thoughts as well. I was hoping that I was missing something, which wouldn't be surprising.

      Maybe I'll submit a feature request to Ninox.

    • Business Analyst
    • Terry_Hopper
    • 1 yr ago
    • Reported - view

    I had this problem too.

    I have submitted this as a feature request to Ninox.

    In the meantime, I worked around the problem by looping through the table and generating a custom HTML table—instead of using a View element. Obviously, the user can't click on a row and pop to the record, but I didn't need this for my purposes.

    • David_Gyenes.1
    • 1 yr ago
    • Reported - view

    I had the solution to this but with the new update that is coming takes that away. Still you can maintain the current viewing record easily. You can set a hidden Text field (Curr_Table_Rec_Id) on Table (Settings) that you never use for the users. On the Tables where it matters use a Tab element Trigger On Open and write into this "Global Variable" (record(Settings, 1).Curr_Table_Rec_Id := raw(id)). You can check the value very quickly with the record(Settings, 1).Curr_Table_Rec_Id to link your View element. On Trigger On Leave erase the value (record(Settings, 1).Curr_Table_Rec_Id := void).

    Terry Hopper I did the same, custom HTML table to be able to open unrelated records and copy values. That still works till they release 3.8 by simply inserting into your HTML element onclick='ui.popuprecord(""" + raw(id) + """)' where the raw(id) is the alphanumberic id of the table record like "A1" or / and the onclick='database.update(TABLE_REC_ID, FIELD_ID, VALUE)' to write into a field (ex: database.update(""A1"", ""B"", ""Hello World"")) -> Table A, Record 1, FieldId B. These small features were so powerful...

      • Business Analyst
      • Terry_Hopper
      • 1 yr ago
      • Reported - view

      David Gyenes . Oh, that's a good idea. I wish I'd thought of using ui.popupRecord() in the HTML.

      This should still work in 3.8.0—Ninox's note said "we will provide limited backwards compatibility for" both database.update and ui.popupRecord, although "The use of these functions is therefore not recommended and we will disable them in the upcoming releases".

      Hopefully, another way of doing this will be released before these are permanently disabled.

      • Fred
      • 1 yr ago
      • Reported - view

      Terry Hopper What notes from Ninox are you referencing?

      • Business Analyst
      • Terry_Hopper
      • 1 yr ago
      • Reported - view

      Fred That was in the notes for Beta testers.

      • Ninox partner
      • RoSoft_Steven.1
      • 1 yr ago
      • Reported - view

      Do you know if database.update and database.create still works? I didn't get it working. How do you get the fieldID? Just the name of the field or the internal Id of that field? Do you have an example? TIA.

      I know the following code still works : (but with a warning in the console, it might not work in future updates)

      ui.openRecord, ui.popupRecord, ui.openHome,...

      • Ninox partner
      • RoSoft_Steven.1
      • 1 yr ago
      • Reported - view

      Ok, found the solution. It must be the internal ID of the field. (A or B or C......)

    • Fred
    • 1 yr ago
    • Reported - view

    Now that this post has been revived, I can share what I have figured out in the mean time.

    As some of you know I have a sports DB. Which means there are lots of stats that need to be generated.

    I have created dashboards to get insight into my data, which means I have many views on the dashboards. We can take one example. I track two team show jumping leagues. So one of my dashboards is a team level comparison.

    I have the following tables:

    Team

    League Dashboard

    On my League dashboard I have the following fields:

    League

    Season

    City

    Each of these fields affect the data shown on the dashboard.

    I have a view element that gathers the appropriate team records from the Team table. Easy. At first I would create a column for each stat. One of them is Average Penalty (or AvP). With the three fields above I had to add logic to each formula column in the view to keep track of it. Which means if I had to modify my code I had to do it to each column.

    For example each column would have something like:

    let xDsh := first(select 'League Dashboard');
    let xLeague := record(Leagues,number(xDsh.League);
    let xSeason := record(Seasons,number(xDsh.Season));
    let xCity := record(City,number(xDsh.City));
    switch xDsh.mode do
    case 1:
        (select Results[League = xLeague and Year_calc = xSeason.Year]).Penatlty4Average)
    case 4:
        select Results[League = xLeague and 'City Name' = xCity.'City Name']
    case 5:
        select Results[League = xLeague and Year_calc = xSeason.Year and 'City Name' = xCity.'City Name']
    default:
        select Results[League = xLeague and ]
    end
    

    In addition I learned that this method also had a high overhead and would cause some dashboards to take 30 seconds or more to load.

    Thinking that I was already in the Team table I thought I would move all of the work load to that table.

    So I added a tab to my Team table form, called it Stats, then created the following fields:

    allResults

    ATDResults

    ATDAvP

    There are many more, but you get the idea.

    The allResults field is where I do the initial data gather. I don't need a select as I have links here but you can if you don't already have the initial link.

    Then in ATDResults, I put:

    let xDsh := first(select 'League Dashboard');
    let xSeason := record(Seasons,number(xDsh.Season));
    let xCity := record(City,number(xDsh.City));
    switch xDsh.mode do
    case 1:
        allResults[Year_calc = xSeason.Year]
    case 4:
        allResults['City Name' = xCity.'City Name']
    case 5:
        allResults[Year_calc = xSeason.Year and 'City Name' = xCity.'City Name']
    default:
        allResults
    end
    

    Then in ATDAvP I put:

    avg(ATDResults.Penalty4Average,1)
    

    Then back in my League dashboard in the view element for Teams I can select the field ATDAVP as a column choice.

    Now whatever I choose for the fields League, Seasons, or City, all my stats will change appropriately. If I need to change my code because I want to add a new selection then I only need to update ATDResults instead of each stat field.

    I hope this helps and I look forward to seeing what others have figured out.

      • Fred
      • 1 yr ago
      • Reported - view

      I found that moving in this direction would just move the load onto the other tables and make those tables slow to respond. So I've moved away from this and now use use the record() command to get the base results from the dashboard then filter it by each record in the view.

      Using the same example above, I still have in my league dashboard a field called "allResults" that would be the base of records that I would then filter by Team.

      Then in the view I use this code:

          let xTeam := this;
          let xDash := record('League Dashboard',1);
          let xAvP := avg(xDash.allResults[TeamID = xTeam.TeamID].Penalty4Average_Count);
          round(xAvP, 1)
      

      I use the record() command to get the dashboard data then I can use the data in allResults in the view element.

      This way all the work stays in the dashboard and if I need to view the Teams table it can load quickly.

      Still a work in progress. My Riders dashboard still takes way too long to load, many more records to filter then figure out the data. I've tried moving to a script that will create new records in a table and copy the calculated data into number fields and then show that. Will let you know how that goes.

    • David_Gyenes.1
    • 1 yr ago
    • Reported - view

      Sorry for the late respond. I haven't tested it lately. I got the IDs from the schema by just right click and inspect then digging in the schema... you might can do it also with APIs to see the IDs.

Content aside

  • 3 Likes
  • 1 yr agoLast active
  • 13Replies
  • 827Views
  • 6 Following