Created A Dynamic Sequence of Table Rows as you move rows around

1

I wanted to share this, as I Have not found a solution searching the Forums. It may already be known, but you can create a dynamic sequential numbering system of the rows order from top to bottom of a table, even if you move up or down, delete or add rows to the table.
It took three formula columns.

Formula Column 1 = Create a unique Id from a data columns name + rowID()
concatenate(thisrow.[Name],LeftPad(RowId(thisrow),6,“00000”))
Using LeftPad() with enough zeroes insures you get a Unique ID for the next step.
Replace Name with your main column id.

Formula Column 2 = Create a list of the Column 1
concatenate(“,”,list(thistable.[Column 1]),“,”)
Adding the commas at the end and front of the list will help in the final step.
Do not sort the list, leave as default table order.

Formula Column 3 = Create the sequential numbering from 1 to last number.
split(left(totext(trim(thisRow.[Column 2])),Find(totext(trim(thisRow.[Column 1])),totext(trim(thisRow.[Column 2])))+Length(totext(trim(thisRow.[Column 1)))-1),“,”).Count()
What you are now doing is Counting the number of commas from the left trim Column 2.
You left trim it using the Column 1 find() formula. Which means every time you move data up or down the table, it dynamically renumbers correctly. I use this in a template process table that can have multiple processes moved around based on our mfg builds and assembly instructions to represent the order of those processes.

Again, if there is an easier way, please link me to it, but this is working great so far, and I wanted to share for anyone who needs it.

2

Hi! I reproduced your solution in my doc to understand how it works. Could you please explain why you didn’t use a much simpler solution with just one formula, like:
thisTable.Find(thisRow) ?

2 Likes
3

Did not even think the Find would return the number.

2 Likes
4

TheFind() function is used to find the position of an element in an array. In programming, indexing usually starts at zero, but in Coda, indexing starts at 1 — which is simpler and more intuitive. In the formula above, the array is actually the table itself (thisTable ).
So, the formula returns the position of each row in the table, which essentially matches the concept of auto-numbering.
Nevertheless, your approach reflects your great enthusiasm and curiosity. In fact, you reached the result, just in your own creative way. :sunglasses:

2 Likes
5

Appreciate the help. I usually use forums and those that are more educated on programming to learn off of, and have never actually taken classes, but just learn through hard knocks, reverse engineering others work to an extent to reproduce what i need. From Excel, to SQL. And CODA has been a a godsend for what we are doing at our smaller company in lieu of an actual MES tool.

2 Likes
6

If I understand what you’re trying to do correctly, here is a much easier way to do it. I use it often to create unique IDs for each row. Sometimes concatenating it with letters and special characters.

if(
  rowid(thisRow) = 1, 1, max(rowid(thisRow)) + 1)
or 
thisTable.Find(thisRow)

Here’s a working example:

7

Okay, now I remember why i was working on an alternative. If you sort the table it looks at the table in row id order and there is no way to not sort at all, which can cause issues with what I am trying to do. Any solution to that? I may be missing something easy again. Even my solution runs into this issue.

1 Like
8

Never mind, used sort in the formula and not at the table level.

find(thisrow.[column1],sort(thistable.[column1])).

Long story short, I am working on creating a dynamic PDF for one of quality departments where we need certain table data as the header and footer for each page, and if the serial unit scan list creates 4 pages, we need to “separate” the scans per page to have that header/footer on every page wrapped around the list. I have not found a way to create headers or footers for PDFs in Coda and can’t find a thread with a solution, just questions. I believe it is not a current function of CODA. This piece will be the final one in my creative solution lol.

2 Likes
9

Could you show an example of how this should look?

1 Like
10

It is a standard document for our business that we may use for any of our 40+ customers.
The top part is project info that is unique to that work order and the bottom is serial numbers that are unique on each build. EAch page needs to have the header information show, and CODA currently doesn’t have a way to tag an area of the display as header/body/footer. We are having to manually fill out a word/excel, where it is easier to fubar plus I am moving it to coda for cross doc synergy so to speak with other information as necessary for quality control. BTW, i did find a way to accomplish this, and am now just stress testing it.

image
image974×674 16.7 KB

2 Likes
11

Wanted to put what I did as a work around for now.
I created 4 Tables
HeaderTable
DynamicListTable
FooterTable
PageSelectTable

HeaderTable–only 1 Row!!!
Columns for the dynamic Header data you want to have
Column for max lines on a page {[MaxPerPage]}.
Any other objext you want diplayed on header (images, links etc.)

DynamicListTable --(you can set this up as a form for line by line entry, which i prefer for control and allowing multiple people access to enter data at once if necessary)
Columns for the dynamic line data you want to have
RankColumn-- Formula to rank each row sequentially {Find(thisrow,thistable) or if you want to sort the data as well, then use Find(thisrow,(thistable.sort()))}
PageColumn–Formula {roundup(thisrow.RankColumn/HeaderTable.MaxPagePer)}

PageSelec Table
Column for total pages {[PageCount]}
Column for page select {[Page Number]}

FooterTable–Only 1 row!!!
Columns for Dynamic data of the footer
FooterColumn concatenate all the footer dynamic data (use character(10) for line returns, " ", character(9) for tabbing, etc in the concatenate formula.

Go back to Header Table
Add 3 columns
PagesColumn–formula {concatenate(PageSelect.PageNumber," of ",thistable.PageColumn.max)}
FooterData Column–formula({FooterTable.FooterColumn})
LineData Column–Canvas Column to link a view to the DynamicListTable
In this table arrange the columns for the dynamic data you wnat displayed for the entries, and set the filter with a formula to hide non selected pages ({PageColumn=PageSelectTable.PageNumber}).

Then create a Detail View of the HeaderTable and design the PDF for print out (it is limiited to 4 columns wide for the header data and you have to figure out how much the dyanmic data will expand its column in the detail view page ton insure it is only one page printout to one page number selected.

If i get time i will share a default doc for using as template.

I also have another way if you want to create columns of dynamic data. Will share that one next.

Again, unless someone has a better way, this is what i am using to mimic headers and footers for PDFs etc.

1 Like
12

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.