0

Relative record numbers in a table

I want to grab a subset of a table. Then display the subset in a view and have the view numbered from 1 to the count of records in the view. In other words, I want the view to have a field containing the numbers 1 through 10 if the subset view has 10 total records. Seems like this should be simple, but I’m just missing something!

14 replies

null
    • John_Halls
    • 4 mths ago
    • Reported - view

    Hi  Is this what you are looking for?

    https://docs.ninox.com/en/tutorial/basics/row-numbers-and-ids

    They call this the "always visible row number" not to be confused with the record id.

    Regards John

    • szormpas
    • 4 mths ago
    • Reported - view

     Hi    If I have understood what you want correctly, one solution I have come up with is the following:

    let t := this;
let arrayIDs := for loop1 in (select Table) order by number(Id) do
        loop1.Id
    end;
let i := 0;
while item(arrayIDs, i) != t.Id do
    i := i + 1
end
;
i + 1
      • szormpas
      • 4 mths ago
      • Reported - view

        The subset of the table must be the same both in the View and in the above code within a formula column of the View.

    • brentjl1
    • 4 mths ago
    • Reported - view

    I saw that. But that refers to tables! I think I’ve got it figured out. What I wanted was ROW numbers in a View. The default view has ID numbers as the first column and NO ROW NUMBERS. When you are using a view as a temporary window into a table that will be erased and repeatedly repopulated, it’ll come up with ever increasing ID numbers and no row numbers. I was able to force a formula in the view back into a 1, 2, 3, to x format of row numbers by subtracting the view’s first row id number from each row’s id number which gives a formula with a 0, 1, 2, to x-1 value then simply add 1 to attain a field that gives the view’s row numbers formula the required 1, 2, 3, to x format. Thank you for responding!

      • szormpas
      • 4 mths ago
      • Reported - view

         ,

      Could you please share your code that you use within the formula column?

      Ninox doesn't display row numbers in view elements by default, so it's useful for all of us to have the best workaround.

      • brentjl1
      • 4 mths ago
      • Reported - view

       Formula used is:  

      let xRow := number(Id) - number(first(select Bracket5).Id) + 1;
      xRow

      This only works in my special case where the table is erased and then regenerated for each pool tournament. Because the table is generated automatically by a BUTTON formula that creates all records in the table in one go, all are sequential, just not starting at 1. When the table is erased and reused, Ninox remembers all the previously generated records and the id numbers just keep climbing. I use a view in a different table record to peek back at this temporary table, but when I do that I have to dial back the id numbers of the records created. The above code does that. Again, this only works if the record id’s in the viewed table are sequential!

      • brentjl1
      • 4 mths ago
      • Reported - view

       And i realize now that I wasn’t specific in the Subject line that this is a view that I’m trying to renumber the rows. I called it relative row numbers in a table by mistake!!!

    • Nick
    • 4 mths ago
    • Reported - view

    Maybe this old post helps...

    https://forum.ninox.com/t/60hrm2r

    • szormpas
    • 4 mths ago
    • Reported - view

     

    As you say, it's a special case and I am not sure if I completely understand your code.  I think your post has brought to the surface an interesting problem to solve. As I mentioned before Ninox does not display row numbering in a View element.

    My solution above refers to a View element in a form or a Page.

    So, my question is what is the best workaround if we want to add a  row numbering column to a View element in a form or a Page?

      • brentjl1
      • 4 mths ago
      • Reported - view

       I’m only going to address my code (for sequential id number records in a view). I’m a hobbyist, taking Ninox as far as I can to suit my needs, but with no specific education or background in programming or DB’s. My code only works if record id’s in a view are sequential and relies on the fact that I can use a formula to trim the id number of each record back to point where the row number formula field now represents row number position as desired. The first row subtracts the first row’s id number from itself to get 0 value, then adds 1 to leave the first row number formula field represented as 1. Subsequent rows subtract the first row’s id number from the subsequent row’s id number to get the relative position in the view then adds 1 to that value to get a value that reflects a “1, 2, 3,… x“ format as opposed to the relative “0, 1, 2,… x” format. Hope that explains the logic of the code. This works for my specific circumstance due to the consecutive nature of the records in my view. A loop based solution such as yours will have to be implemented for non-sequential id number records in a view. I can’t make a recommendation on the “best” way to attain this. Is outside my experience level. I should have realized sooner that I could make a simple formulaic adjustment to the id# that would suffice, but just didn’t see it till after I posted the help request. I was stuck on thinking that there must be a function that would give the relative row position of a record in a table or view. Only thing I could find was index() which gives the relative position of a record where you are looking for a search term. Not what I was trying to do. Hopefully there’s enough response to this request that you can compare and contrast different approaches to a solution to row numbering views.

    • John_Halls
    • 4 mths ago
    • Reported - view

    Hi all.

    Be aware that a select statement in a formula needs a reason to trigger, usually when a non-formula field changes in value, but it's always a risky strategy. I would only use a select statement in a button or trigger.

    Regards John

      • szormpas
      • 4 mths ago
      • Reported - view

        Hi, is there any way to avoid the select statement in this particular problem?

      • brentjl1
      • 4 mths ago
      • Reported - view

       

      If I understand you correctly, I may be getting myself in trouble using select statements in formulas? I hadn’t run into problems with this in past use but I’m a hobbyist and haven’t had to deal with multiuser or huge DB environments. I’m assuming this is “not recommended” due to time delay issues in large DB’s. I’ve seen some of this in certain DB’s. Just want to know if this is simply a DB time response thing or if it could affect the accuracy or stability of the DB.

    • szormpas
    • 4 mths ago
    • Reported - view

    Hello  ,

    I think we've got to the bottom of the issue. We were both trying to solve the same problem!

    Your code works perfectly in the case where the record IDs are strictly sequential, i.e. no deletions have been made to the table since its creation.

    On the other hand, I tried to come up with a solution for the case where deletions have been made, so that the IDs are no longer consecutive.

    Both solutions use a select statement, which I agree with  isn't the best approach.

    Best wishes

Login to reply

Content aside

  • Status Answered
  • 4 mths agoLast active
  • 14Replies
  • 78Views
  • 4 Following