Slice? Formula? Struggling a bit..

AppSheet AppSheet Q&A
1

Hi there,

Still new to AppSheet, and loving it! I have been struggling with how to achieve a specific outcome.

I have a dataset, and I would like to isolate numbers in specific columns based on two conditions: the value in the “row” column needs to be equal to ONE (or TWO, or THREE), and they all must have the same reference from the REQ ID column. I have attached a screenshot to illustrate.

I can create a slice where [Row]=“ONE”, but that includes everything where the value of “row” is equal to one, including other records that have a different REQ ID. I have not been able to get a the expression quite right for the slice so that it only pulls two rows from the section of the table in the green square.

USECASE

I am building an app where users have to periodically return to a data set, at multiple points in time, and record the values for 5 different columns. Each “row” represents one check at a point in time. The data is cumulative, , which means, it’s not enough to refresh the data - they have to capture new values with each check. I need to sum the cumulative values of each check so they know when they hit their ceiling. When the sum all of the values equals 100, they can stop doing further checks.

Hoping someone has insight. I’ve really struggled with this for days, and I can’t get the right combination of REF_ROWS, SELECT, FILTER or a properly configured slice to achieve the desired result.

Screen Shot 2022-08-02 at 12.58.57 PM.jpg
Screen Shot 2022-08-02 at 12.58.57 PM.jpg1866×1730 299 KB

2

First, a slice has nothing to do to your need as far as I see.
Second, I’m not sure it’s possible to do the way you expect.

The columns with numbers should update automatically?

1 Like
3

I’m going to assume the table’s name is Table.

I’m going to assume the user’s new data points will be captured in a new row of Table.

I’m going to assume the sums you mention are columns of Table that aren’t visible in your screenshot, perhaps because you intend them to be virtual columns?

Add a virtual column named (e.g.) Prior Entries with an App formula expression of:

FILTER(
  "Table",
  AND(
    ([_THISROW].[Row] = [Row]),
    ([_THISROW].[Req ID] = [Req ID]),
    ([_THISROW].[_ROWNUMBER] < [_ROWNUMBER])
  )
)

Add a virtual column named (e.g.) SumA with an App formula expression of:

SUM([Prior Entries][A #])

Add a virtual column named (e.g.) SumB with an App formula expression of:

SUM([Prior Entries][B #])

Continue this pattern to create SumC, SumD, and SumE.

1 Like
4

Thanks for sharing!! I am going to let that post sink in and try it tomorrow! I appreciate the creativity. :slightly_smiling_face:

1 Like