Automatically create new row based on 2 conditions

[Julie-Anne97]

Hello all,

I would like my app to automatically create 

The closest idea I can think of is a form of a for loop but I'm not sure to what extent that's possible in Appsheet. It would go something like this:

for row in table:
if [column 1 ] = [column 1 ]:
if [colomn 2] <> [column 3]:
create new row where [column 2] has value of [column 3]

Don't hesitate to let me know if I'm not clear

Many thanks

[scott192]

Not sure about your [Column1] = [Column1] logic as I think that will always evaluate to TRUE.

You don't say whether you wish the NEW row to be created in the same table or in a different one?

[Julie-Anne97]

The same value in [column 1] should appear in 3 different rows.

The way to differentiate these same 3 rows is that there should be 3 different values in [column 2] (eg : past , present , future).

If there is only 2 rows with the same value in [column 1] , I want to create a new row with the missing value in [column 2]

I would like this row to be added in the same table

[scott192]

It sounds that you need a workflow that runs on the creation of an initial (Past) record.  This workflow should add 2 more records, 1 with a (Present) value in Column 2 and a copy of the value from Column1 from your trigger record.  The second added record with a (Future) value in Column2 and a copy of the value from Column1 of your trigger record.

My assumption is that all three records will be created at the same time with the workflow and therefore record 2 and 3 do NOT exist before you add the first record?

[Julie-Anne97]

Yes exactly I would like to create some sort of a workflow (In the process tab in Appsheet I suppose)

However my database is sometimes complete and sometimes not. So it is possible for the three records to already exist, in which case the workflow should not be activated.

[scott192]

Ok, for each record in your table, you will need 3 Virtual  Columns to indicate whether the other 3 records exists or not.

VC 1 will probably have a formula like this:

SELECT(TableName[ID],AND([_THISROW].[Column1] = [Column1],[_THISROW].[Column2] = "Past"))

VC 2 :

SELECT(TableName[ID],AND([_THISROW].[Column1] = [Column1],[_THISROW].[Column2] = "Present"))

VC 3:

SELECT(TableName[ID],AND([_THISROW].[Column1] = [Column1],[_THISROW].[Column2] = "Future"))

The answer returned by each one of those VCs will be either BLANK (Record does NOT exist) or NOTBLANK (Record already exists)

You could create 3 Slices to show each of the 3 types of missing records where the ROW FILTER condition on each slice would be ISBLANK([VC1] or ISBLANK([VC2] etc to get a list of records for those conditions.

You could then use those Slices in 3 Scheduled bots that adds each of your records at a set time every day?

[Julie-Anne97]

Okay thanks for your response. I think I'm almost there but the result of my slices are almost always TRUE even when the record is not missing.

Because when I make a slice ISBLANK([VC1]) for example, it will appear as blank in row 2 and 3 even though it exists in row 1. Is there any way to make a slice such as this one that takes into account the similar values in [column1]?

Thanks a lot, I'm very appreciative for your help.

[scott192]

---

@Julie-Anne97 wrote:

Okay thanks for your response. I think I'm almost there but the result of my slices are almost always TRUE even when the record is not missing.

---

You will need to clarify this 🙂

Saying almost always suggests there are cases when this is not true?  Let's find out why this is?

[Julie-Anne97]

Okay, here is a table summarizing what I did following your suggestions. In black is my table columns and in blue is the result of my slice. 

This is what I meant to say about the results of my slice always being True. What I would like is :

Slice1 : ISBLANK(VC1past) to show that value3 is missing
Slice2 : ISBLANK(VC2present) to never be blank
Slice3: ISBLANK(VC3future) to show that Value2 is missing the value 'future'

I probably missed a step along the way, I hope the table clarifies where I'm at 🙂 Thanks again for your help!

[scott192]

Have you shortened the Slice syntax to quickly mock up a screenshot?  I wanted to check that you are using ISBLANK([VC1past]) rather than ISBLANK(VC1past)?

Also, what are the settings you have in here?

[Julie-Anne97]

Yes I shortened the syntax, this is how my slice looks 

My option settings look the same as yours I believe 

[scott192]

Ok...well, let's try a different way to check if the records exist or not.

Wrap your VC formulas on your table with a Count().  This should evaluate to 0 if the record doesn't exist and be >0 if it does.

COUNT(SELECT(TableName[ID],AND([_THISROW].[Column1] = [Column1],[_THISROW].[Column2] = "Past")))

Make your slice filters change to [VC1] = 0 etc

It's the only thing I can think of to try as I do believe the logic of what we are doing is correct. 

[Julie-Anne97]

Hello Scott,

When I added wrapped my function with a COUNT I had to change my column type to number because I got this error "The expression is valid but its result type 'Number' is not one of the expected types: List".

However once I had changed my column from list to number and wrapped my VC function with COUNT(), the result was always 0 even when the condition was checked and worked with the SELECT() function previously. 

Using a sequence turned out to be simpler for me with two virtual columns :

VC1 = IFS([Column2]="Past", 1 , [Column2]="Present", 3, [Column2]="Future" , 5)

VC2 = SUM(SELECT(TableName[VC1],[_THISROW].[Column1]=[Column1]))

I was able to create a workflow to create missing rows with the filter condition of my workflow depending on the result of VC2.

Thanks again for your help, I couldn't have gone as far as this without your feedback.

[JJ_TechSupport · Accepted solution]

I'd come at this in this way -- using a quasi-Fibonacci sequence (1, 3, 5) for each of the values (Past, Present, Future) I'd create logic to asign a sum value based on which items or combinations are stroked.

• 0 = No values.
• 1 = Past
• 3 = Present
• 5 = Future
• 4 = Past, Present
• 6 = Past, Future
• 8 = Present, Future
• 9 = All

This way based on the value I know what is needed.