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?
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.
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
- Set a flag on the current record which the formula in the view can pick up in a select statement.
- Loop through the view data and add a link to the current record.
Both have their pluses and minuses.
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.
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...
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:
On my League dashboard I have the following fields:
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:
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:
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.
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.
- 2 mths agoLast active