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!
Solved! Go to 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.
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:
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
Easy. If you want to have the last row per item then you can create a different slice for each item.
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.
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.
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.
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
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?
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.
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.
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.
User | Count |
---|---|
25 | |
24 | |
24 | |
23 | |
16 |