_ROWNUMBER Expression Query

[MauriceWhelan]

Hi. I am hoping someone can provide a solution for the following scenario.

I have a parent table called Tasks and a child table called Task_Details. If I create a task and add 2 task_details I see Row # 1 and 2 in the Related_Task_Details column on my Detail View. If I create another task and add 2 task_details I see Row # 3 and 4 in my Related_Task_Details column.

What I am hoping to display in my Detail View for the second task entered is the row number for each task_detail as it relates to the associated task as opposed to the row number in the Google Sheet. i.e. instead of 3 & 4 I want to display 1 & 2 again.

Is there a way that I can achieve this through an App Formula for the _ROWNUMBER column?

Hope this makes sense.

Thank you.

[Suvrutt_Gurjar]

If I have understood your requirement, it could be done with a VC with an expression something like below

Say the VC is called [Task Serial Number], then the expression for the VC could be something like

COUNT
(
SELECT(Task_Details[_ROWNUMBER],
AND([_ROWNUMBER]<=[_THISROW].[_ROWNUMBER],[Task id]=[_THISROW].[Task id])
)
)

[Task ID] is the Ref column in Task_Details table. The expression assumes that each new task detail gets added below the earlier task in back end Google sheet that anywy happens when a record is stored in back end Google sheet.

[MauriceWhelan]

Thank you so much @Suvrutt_Gurjar. I will test this suggestion.

[Suvrutt_Gurjar]

Hi @MauriceWhelan,

Sure. Please do and please update if it works the way you want.

[MauriceWhelan]

Hi @Suvrutt_Gurjar

Just to confirm your suggestion worked perfectly. I have marked it as solution also.

Many thanks again.

[Suvrutt_Gurjar]

Thank you for the update @MauriceWhelan. You are welcome.

[MauriceWhelan]

Hi @Suvrutt_Gurjar

I was testing this functionality a little more and I came across a little issue. When I create a Task with, for example 3 task details, the expression shows the Count # as 0,1,2 which is correct. I then save the record and go back in to Edit the task. The only change I make is to increase the quantity of task detail # 2 & 3 and when I return to the Task form the Count # are showing as 0,2,3.

I am not sure why this is happening. Could you maybe advise? Is it the way that the count rows are being stored in the Google Sheet maybe? I have attached a screenshot for the create and edit actions and also for the Google Sheet (Task Details Tab) as it was when the record was created.

Thank you.

Maurice.

[Suvrutt_Gurjar]

Hi @MauriceWhelan ,

Could you please share what expression you are using, screenshots of expression results in test pane and table details with columns names?

The screenshot shows row number 10 with Count # as 1 and row number 9 as 2. The expression is based on position row numbers, so not sure why earlier row is showing higher count. We could analye that aspect after you share the requested details.

[MauriceWhelan]

Hi @Suvrutt_Gurjar

The expressions I am using is
COUNT(
SELECT(Fence_Count_Original_Pack_Quantities[_ROWNUMBER],
AND([_ROWNUMBER]<=[_THISROW].[_ROWNUMBER],[Record ID]=[_THISROW].[Record ID])))

Task Table Name: Fence_Count_Master

Task Details Table Name is: Fence_Count_Original_Pack_Quantities
Column (that is storing the count number in the wrong sequence) is: [Count #]

Test Results screenshot for the expression is attached. Column headings are
[RowNumber]
[Count ID]
[Record ID] (IsPartOf Fence_Count_Master Table)
[Original Pack Quantity]
[Count #]

It certainly seems the issue is being caused by the way that Google Sheets is storing the rows in the Fence_Count_Original_Pack_Quantities table.

Hope this data helps. Please let me know if I have omitted anything.

Maurice.

[Suvrutt_Gurjar]

Hi @MauriceWhelan,

Thank you. Could you please elaborate , why you mention below?

MauriceWhelan:

It certainly seems the issue is being caused by the way that Google Sheets is storing the rows in the Fence_Count_Original_Pack_Quantities table.

Also results in the test pane look good to me.

Also is the [Count #] real coulmn or VC? My suggestion will work with a [Count #] as a VC.

[MauriceWhelan]

Hi @Suvrutt_Gurjar

Below is the entire Google Sheet and as you can see for rows 2 - 4 the Count # is not in sequence. The other issue I noticed in the example test results screenshot is when I changed the quantity for row # 9 and row # 10 the [Count #] value changed from 1 to 2 and 2 to 3 respectively. Originally I had 0,1,2 on creation but that changed to 0,2,3 when the edits were made.

I can confirm the [Count #] is a real column, not a VC. I will follow your suggestion and try it as a VC and revert.

Many thanks.

[Suvrutt_Gurjar]

Hi @MauriceWhelan,

Thank you. Yes , please test it with a VC. As I mentioned , the suggestion will work with VC.

Suvrutt_Gurjar:

If I have understood your requirement, it could be done with a VC with an expression something like below

[MauriceWhelan]

Hi @Suvrutt_Gurjar

I am now implementing the functionality with [Count #] as VC however the way the individual [Count #] rows are being submitted to the table are causing an issue. I have attached a screenshot which shows the following (from left to right)

1. Fence_Count_Master form with the Related_Fence_Count_Original_Pack_Quantities details in order 0 - 4 (Which changes to 1 - 5 when all the rows are synced).
2. The test results for the expression in question.
3. The Google Sheet were the related Count # are submitted to.

The problem I have is that after the record is saved and the details are synced to both tables the order of the related counts bare no resemblance to the order they were originally captured in. It is essential that they are always presented in the order that they were captured.

Is there any additional advice you could provide to help with this?

Many thanks.

[Suvrutt_Gurjar]

Hi @MauriceWhelan

MauriceWhelan:

Fence_Count_Master form with the Related_Fence_Count_Original_Pack_Quantities details in order 0 - 4 (Which changes to 1 - 5 when all the rows are synced).

I believe this is expected behaviour, I mean rows being numbered from 1 to 5 after sync. The row numbers will stabilize after sync as each new row gets added.

MauriceWhelan:

The problem I have is that after the record is saved and the details are synced to both tables the order of the related counts bare no resemblance to the order they were originally captured in. It is essential that they are always presented in the order that they were captured.

Can you please elaborate the above? Especially “related counts bare no resemblance to the order they were originally captured in”?

I believe the row numbers , that is [Count #] VC will be numbers in the same order as the backend Google sheet records are stored. So if related record1 is row number 10 in G Sheet and record 2 is row number 11 in G sheet, the [Count #] will be 1 for row number 10 and 2 for 11

[Suvrutt_Gurjar]

Also is the [Count #] now a real column or VC?

[MauriceWhelan]

Hi @Suvrutt_Gurjar

Thank you for that. I would agree with your comment re rows stabilizing after sync.

The attachment in my last post was for the very first records posted to the table, however, if you look at the order of the numbers in the data capture form i.e. they were captured in the following order 5 (Count 1),10 (Count 2),15(Count 3),20(Count 4),25(Count 5). When you look at the order in which they are displayed from the expression ‘Test’ result they are in a different order i.e. 15(Count 1),25(Count 2),10(Count 3),5(Count 4),20(Count 5).

I can confirm the [Count #] is a VC.

I hope this helps explain my query better.

[Steve]

You shouldn’t be using _ROWNUMBER at all, it wouldn’t seem to me. Add a new normal (not virtual) column named Serial of type Number to the Task_Details table with an Initial value expression of:

(
  MAX(
    SELECT(
      Task_Details[Serial],
      ([_THISROW].[Task] = [Task])
    )
  )
  + 1
)

[Suvrutt_Gurjar]

Thank you.

MauriceWhelan:

The attachment in my last post was for the very first records posted to the table, however, if you look at the order of the numbers in the data capture form i.e. they were captured in the following order 5 (Count 1),10 (Count 2),15(Count 3),20(Count 4),25(Count 5). When you look at the order in which they are displayed from the expression ‘Test’ result they are in a different order i.e. 15(Count 1),25(Count 2),10(Count 3),5(Count 4),20(Count 5).

Which one is expected correct behavior - numbers shown in data capture form or displayed in test?

[MauriceWhelan]

@Suvrutt_Gurjar

The numbers shown in the Data Capture Form.

[MauriceWhelan]

Thank you @Steve. I will test this expression.