Re: Conditional formatting twice conditions

aminsaleh · 04-29-2022 07:59 PM

Hello community, I am new to AppSheet but I have a rigid computer background. I've built a small app to manage sales and I'm here to post a question regarding the conditional formatting rule in AppSheet and whether is it possible to write two conditions from two different tables.

Here is a pseudocode to get an idea:

*****

If (Seller picks a product X In salesman table)

&&

If ( Product X quantity is less than 10 in stocks table)

Then: The product X text becomes red.

*****

Below is the attached screenshot of my app:

What I need to accomplish is simply the followings:

1- On the top side, A seller named Hassan goes to his tab or table "Hassan" and clicks the plus sign to add a new order.

2- On the new order page there is a list called "order1" and he has to select one product.

Now here is the multiple conditional formatting I am questioning about:

((

If "order1" in "Hassan" table = Basic chair && If "item Qty" in "Stocks table" <10

Then: The basic chair on Hassan's table should be formatted for example in red font.

Thanks indeed

Ryan_Mortimer

I don't think I completely understand what you are asking, but to get you started here are my thoughts:

The best thing to do is to achieve what you need, even if it is a very convoluted way - THEN you go in and try to optimise and improve - this is the BEST way to learn.

But for a starting point:

I would set up some Virtual Columns / Reference Columns to drag the required values into the table that you are formatting (you can then bring in additional informational or column values with dereferencing

Then you can simply use this expression to achieve your formatting.

AND([ORDER1] = "BASIC CHAIR",[ITEM QTY] < 10)

Once you get that behaviour working, you can then work towards doing this in purely an expression.

aminsaleh

@Ryan_Mortimer

Thank you for your kind help. But nothing is working.

>> What proper virtual column settings I should have? For type and element?

>> what is proper formula? is it for example

Stocks[Item Qty]

-----

Also this expression did not work. It has a conflict with list and number.

AND([ORDER1] = "BASIC CHAIR",[ITEM QTY] < 10)

Ryan_Mortimer

I don't think you are wasting your time with AppSheet, I think you just need to slow down and look at the data structure and HOW to achieve it.

Think step-by-step what you need to achieve, then attack each step individiually.

A lot of the times, you will get a straight answer and other times people will make you "work" for you answer. The point is really to help people learn, not just provide solutions for people.

Your feedback also of "nothing is working", what do you mean by nothing is working? Is there an error message, a blank value, the "wrong" value displaying?

Ryan_Mortimer

Before you throw in the towel, why don't you start by getting all the values you need to reference (to that row) displaying in a virtual column.

For example, if you have 8 values you need to draw into your main table, then make as many virtual columns as you feel you need to achieve this.

Once you have perfected that behaviour, then you can complete your expression using "row level" expressions.

THEN if/once that works, go back and start trying to do it a more "complex" way.

TeeSee1

Hi @aminsaleh

https://www.googlecloudcommunity.com/gc/Tips-Tricks/FAQ-FILTER-LOOKUP-MAXROW-MINROW-REF-ROWS-and-SEL...

Should help you create expressions to get data from various tables within you app.

SELECT is most generic.

aminsaleh

@Ryan_Mortimer

Can I give you access to my appsheet and google sheet and you do the steps you mentioned above yourself.

Ryan_Mortimer

I am happy to help where I can, however, I strongly suggest that you attempt my suggestion above:

@Ryan_Mortimer wrote:
Before you throw in the towel, why don't you start by getting all the values you need to reference (to that row) displaying in a virtual column.
For example, if you have 8 values you need to draw into your main table, then make as many virtual columns as you feel you need to achieve this.
Once you have perfected that behaviour, then you can complete your expression using "row level" expressions.
THEN if/once that works, go back and start trying to do it a more "complex" way.

aminsaleh

@Ryan_Mortimer

Hi again and sorry for bothering you, sorry If I was a bit nervous. May be the problem I am not creating the virtual column correctly as I am newbie to Appsheet. Let me walk you step by step to what is happening with me.

1 >> I want to create the virtual column in (Hassan Table). see image below.

2 >> The virtual column should fetch data as it is from the table called (Stocks) and column called (Item Qty). See below image.

3>> Here is the virtual column name I made. see Image below kindly.

4>> In the virtual column formula part I just inserted the (Item Qty) from (Stocks) table. See the three images below please:

5 >> Then I saved and here is the virtual column shown in (Hassan) table. See Image below:

6 >> Here I went to (Hassan) table and wanted to create a conditional formatting formula. Like what you have suggested.

AND([ORDER1] = "BASIC CHAIR",[ITEM QTY] < 10)

But I got below error

7>> I then went to the virtual column and changed from List to Number. still I got error below.

Thank you for your kind help 🙂

Ryan_Mortimer

Definitely not bothering me! The way you have laid it out is a great start. Tackling the issues/solutions one-by-one is really the trick to learning effectively.

I will try and go through these one by one, piece by piece and steer you in the right direction. it might be slow, but we will get there.

First question, is your virtual column returning the correct value? It looks to me that it wouldn't be returning the correct value at all.

You will need a piece of data to tie to the Hassan and Stock tables together. Are there two columns that share the same input? (ie. Item Name, Item Code, etc?)

Or to ask in a more simple way, where would "BASIC CHAIR" be selected in the HASSAN table?

A big hint to make your life a million times easier (and your apps load faster!)

https://help.appsheet.com/en/articles/1090811-dereference-expressions

Ryan_Mortimer

7>> I then went to the virtual column and changed from List to Number. still I got error below.

When you use a SELECT() function, it will return a LIST value, regardless. There are tips around this, but we will get to retrieving the correct value first.

aminsaleh

Thank you @Ryan_Mortimer for the positive way of helping. Regarding your questions:

1 >> First question, is your virtual column returning the correct value? It looks to me that it wouldn't be returning the correct value at all.

How can I tell that it returns the correct value. Let me sow you below screenshot of the virtual column (Item Qty) and how it shows on the simulator.

Note: The values you see in the (Item Qty) in the simulator (10,100,640,100,57,9) are the same in my Google sheet stocks sheet. See image below kindly:

2>> You will need a piece of data to tie to the Hassan and Stock tables together. Are there two columns that share the same input? (ie. Item Name, Item Code, etc?)

I think there is no, there is no two columns that share the same input. Let me show me both tables. Hassan and Stocks.

Hassan Table

Stocks Table

3 >> Or to ask in a more simple way, where would "BASIC CHAIR" be selected in the HASSAN table?

It will be selected in Hassan table, mainly in Order1 and order2 columns. You could see them in Hassan table in 10&14 columns.

Thanks indeed.

aminsaleh

Hi @Ryan_Mortimer , would you mind please see previous reply please.

Ryan_Mortimer

Hey @aminsaleh, sorry for the delay in response. I have been very tied up.

How did you go with this? I will have some free time in the coming days to provide you a thorough reply.

aminsaleh

Hi @Ryan_Mortimer , thank you for following up. Yes, problems have been solved. Thanks to Mr. Aleksi who gave me helpful heads up and tips.

The main problem, ref problem, and other in-between issues all work very well now.

► Because I made the (order name) as ref to (stocks table).

Now in the stocks sheet (inside detail), it shows "add" and "view" text links. I do not want those links to be shown. see the below image please:

► I tried this solution I found today but did not work

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Remove-Add-Button/m-p/272839

Then I went to >> Data>> Column >> and in the stocks column, I hided the view of the ref fields. see below image please:

while It solved the problem for me but If you have better way to sort it kindly help.

******

► Another recommendation for you is to frozen the top column header so that as we drag down we know each field is what. see here please:

Thank indeed