Initial input based on condition previous rows cell

[Jantjo]

Hey there, I've been trying to work on a formula (in my mind I can do it in bash) but stumbling a bit here. What I'm trying to achieve is based on a specific previous select is to auto populate (initial value) remaining questions based on the the previous row matching cells. I would like to add some validation or condition and I think this is where I'm getting hung up on.

Option 1 (just to get it working)

Based on a pre qualifying questions input, if the previous row has a matching qualifying answer, pre-populate with existing rows information. if it doesn't match then just leave blank.

Option 2 (get it working with style 🙂 )

Similar to above.. Based on a pre qualifying questions input, lookup the previous row that has a matching qualifying answer, pre-populate with existing rows information. if it doesn't match then just leave blank.

The intention is to auto populate (reduce repetitive selection) only if its relevant in the repetitive task. Example would be an inventory system for widgets and gizmos. The first question is "what is it". if you select widgets and your previous entry was a widget, it would auto populate the selective questions with relevant information. If you selected gizmos you would then have to manually fill in the remaining questions. For Option 2, this is where it would search for you previous matching "what is it" entry and auto populate with that information. 

So far I have to get the previous row matching cell..

LOOKUP([_THISROW].[_RowNumber]-1, “Widgets and Gizmos”, “_RowNumber”, “Teeth”)

Having issues with condition wrapper of did you select Widget or Gizmo previous (or search for previous)

Thanks!

[Joseph_Seddik]

Hello and welcome to the community!

If I would be continuously looking for the last entry in the same table (lets name it "ourTable"), I would do the following:

1. Create a slice from the table containing only the last row. Let's name it: "lastRowSlice". Its row filter condition should be: [_RowNumber] = MAX(ourTable[_RowNumber]).
   
   
2. Add a new column to your table, lets name it: "lastKey". In this column we will store the value of the key of the last row, using this formula: ANY(lastRowSlice[name of key column]).
   
   
3. Now any and all column values of the previous row are easily accessible using the formula: 
   [lastKey].[name of desired column]

[Jantjo]

Thanks for the response and it a very elegant way of presenting the last data then I was doing before. Just missing the last step of pre-check/search/validation that I'm stumbling on. If I were to break it down a bit more it would look like this.

Q1=Question 1: What is it
Options: Widget, Gizmo
Q2=Question 2: What color
Options: Red, Green, Blue
Q3=Question 3: How many teeth
Options: 3, 4, 5
Q4=Question 4: What's the width
Options: 20, 30, 40

Example Table Entry(s) - rownumber,  datatime(key), item, color, teeth, width
1, datetime, Widget, Red, 3, 30
2, datetime, Gizmo, Blue, 4, 40
3, datetime, Gizmo, Blue, 4, 40
4, datetime, Widget, Red, 3, 30

Based on yours and mine formula, it automatically populates with the last key regardless if the qualifier (item) is matched. Thus you would have to go back and resolve them, removing the value of the initial value

What im looking to accomplish is based on the input of $item, it would present the last match $item data. If its not possible to search the array then just leave blank.

example out based on the above table.

input=Gizmo

Results= 5,datetime, Gizmo, Blue, 4, 40 (or blank if appsheet is not able todo this function)

Input=Widget

Results=5,datetime, Widget, Red, 3, 30

Thanks

[Joseph_Seddik]

Easy. If you want to have the last row per item then you can create a different slice for each item.

1. "lastGizmoSlice", with row filter condition as: 
   
   [_RowNumber] = INDEX( SORT(
     SELECT(ourTable[_RowNumber], "Gizmo" = [item]),
     TRUE
   ), 1)
   
   
2. "lastWidgetSlice", with row filter condition same as above, but using:
   "Widget" = [item] instead, in the expression. 
   
   
3. Add two new ref columns to your table:
   
   
   • "lastWidget", with App Formula:  ANY(lastWidgetSlice[datetime]).
     
   • "lastGizmo", with App Formula:  ANY(lastGizmoSlice[datetime]).
     
     
4. Now, any and all column values of the respective last rows are easily accessible using the formulas:
   
   
   • [lastWidget].[color]
     [lastWidget].[teeth]
     [lastWidget].[width]
     and,
   • [lastGizmo].[color]
     [lastGizmo].[teeth]
     [lastGizmo].[width]
     
     

One last important note:

DateTime is NOT a good key, since you can easily have two records added to your table in the same second.

[Jantjo]

Based on your original suggestion, after some testing I've noticed an issue and I'm not to sure if its the "ANY" condition.
Same example as above:
1, datetime, Widget, Red, 3, 30
2, datetime, Gizmo, Blue, 4, 40
3, datetime, Gizmo, Blue, 4, 40
4, datetime, Widget, Red, 3, 30

If I enter Widget for question 1, it auto populates Red; 3; 30 in the following sections. If I happen to get a different widget that has 8 teeth rather then 3. Then next time I select Widget it populates Red;3,8;30. So it seems to grab all the entered items and not just the last one. if that makes sense. is there away to scope the results to only one row rather then all? 

For your new suggestion; im not to sure if I'm following as it sounds like your creating two data slices for widgets and gizmos, but that would mean you would already know the answer to Q1 and you would set the initial value to [lastWidget].[color] for example. 

Q1=Question 1: What is it?
Q2=Question 2: What color?
Q3=Question 3: How many teeth?
Q4=Question 4: What's the width?

The simple formulas bellow bring in the last entry, but they don't pivot on the qualifying question of Q1, what is it... 

LOOKUP([_THISROW].[_RowNumber]-1, “Widgets and Gizmos”, “_RowNumber”, “color”)

LOOKUP([_THISROW].[_RowNumber]-1, “Widgets and Gizmos”, “_RowNumber”, “teeth”)

LOOKUP([_THISROW].[_RowNumber]-1, “Widgets and Gizmos”, “_RowNumber”, “width”)

This fixes the repetitive task, just need to adjust when you pivot to widget/gizmo or alt entry, was looking to auto populate the last relevant as it should be 95% valid. 

[Joseph_Seddik · Accepted solution]

Please post screenshots of your formulas. Red;3,8;30 is an imposible result using the expressions in my reply. So please show what expressions you have used in your columns.

No, we do not know already the answer, we wait till the answer is filled in by the user, and set the initial value of the different columns accordingly. Here's an example, for the color column

SWITCH([item],
  "Widget", [lastWidget].[color],
  "Gizmo", [lastGizmo].[color],
  ""
)

Your LOOKUP expression that you already included in the first post as you say rightly "do not pivot on the question". If you need to "pivot on Widget/Gizmo entry", you can implement the solution in my replies. 

[Jantjo]

Would this also mean that if I add a new item, I would have to create a slice for each one? If so I think that will be allot of overhead and end-user support. I would have though recalling the last valid row/cell would be easy. LOL

[Joseph_Seddik]

I honestly see it easy, but easy is relative anyway. 

There are many ways to achieve a desired result in development in general. A well-informed choice will always be based on a tradeoff between different factors prioritizing some over the others. 

My choice here is based on prioritizing app performance bringing significant user benefit, on the expense of additional work the creator will have to do every once in a while. 

What is your priority?

[Jantjo]

I think my priority is reducing the end-users repetitive work while maintaining item specific continuity when changing said item. 

My example (or table) is just Gizmos and Widgets but that's not the be all end all. Tomorrow we may add gadgets or doodads as the [ITEM] is an editable field to allow new ITEMS. So as a developer (I'm not by trade which is most likely why im having a hard time at this) its impossible for me to know what [ITEM] they would want next, so creating a slice (unless I can automate that creation on the backend, and automatically update all the initial values to reflect) is not feasible. 
A simple expression of, you selected this X as [ITEM], I'll pre-populate the rest of the columns based on your previous matching X [ITEM]. seems straight forward. 

I've been testing ORDERBY, FILTER, INDEX, TOP, SORT etc.. but I cannot get it functioning as desired, almost.. but the date it provides is not from the correct column. 

Been Struggling with this for a bit now. 

[Joseph_Seddik]

Hi @Jantjo,

I agree, if you will have an increasing list of items, then I wouldn't recommend this solution. This was not clear in your original request.  Anyway, it is a bad practice to use columns for lists. Two items is OK, but more is not. 

I don't like to change other people's existing data structure unless necessary, but here I believe we should. So this is what you can do, also preserving performance and avoiding much complexity in the backend. 

1. You should have a new table listing the Items. Each item will be in a row, and users can add new items as needed. 
   
   
2. The item column in your Questions table should be a ref to the Items table. Accordingly, AppSheet will add a reverse-reference virtual column in your Items table called "Related Questions". 
    
3. Assuming the key column in your Questions table is "questionID", you should then create a slice for your Questions table, let's name it "itemLastQuestion". Its row filter expression should be:
   
   [questionID] = INDEX(
     [item].[Related Questions], 
     COUNT([item].[Related Questions])
   )
   
   In this way, the slice will contain only as many rows as there are items; one row per item. 
   
   
4. Add a normal column to your Questions table, name it "lastQuestion", its App Formula should be: 
   
   ANY( SELECT(
     itemLastQuestion[questionID],
     [item] = [_THISROW].[item]
   ))
   
   
5. You can then set the Initial Value for the three "color", "teeth" & "width" columns, respectively, as follows:
   
   [lastQuestion].[color]
   [lastQuestion].[teeth]
   [lastQuestion].[width]
   
   
6. I would also hide these three columns until the user fills in the item column by adding the following expression in the "Show?" field in each of these column:
   
   ISNOTBLANK([item])

Now, you'll not have to change any formulas or anything else in your app. Whenever you have a new item, all you have to do is to add it in the Items table.