HELP! Conditional filtering with sum in a REF. Or maybe it's something else! XD

[MaurySj]

Hello! First of all, this is translated with google translate. I hope you are well and that you understand.
Second. Thanks so much for reading.

I'm stuck with an expression that I can't put together.

The app is for project management and control, with its requirements and the necessary products and services to complete it.

I have many more tables, but I will only name the ones that intervene.

A table "DEPLOYMENT" that contains all the necessary things for each "REQUIREMENT" of all the "PROJECTS".
In this table ("DEPLOYMENT"), many times, products or services with different quantities are repeated.

Create some sample data:

What I want to do and I can't do is:
Obtain a filtered list, where each product or service only appears once, with its added quantities.

Lick to the spreadsheet with the same data as the images.

Thank you very much in advance for any help you can give me.

[TeeSee1 · Accepted solution]

The easiest way if you are not picky with your display format, then, you can create two virtual columns that concatenate (PROJECT & SERVICE) and (PROJECT & PRODUCT).

You can then configure your views to group on these VC's with GROUP AGGREGATE on [Quantity].

If you want a tabular format, then you need to create tables to extract unique combinations of  (PROJECT & SERVICE) and (PROJECT & PRODUCT). Then create VCs with SUM(SELECT(here get [Quantity] with Filtered by  (PROJECT & SERVICE) )).

Maybe other community experts can propose better solutions..

[TeeSee1 · Accepted solution]

You can write an action, add a new row to another table using values from this row.

In the "Only if the condition is true", set something like

ISBLANK(
 FILTER("report table",
  AND([project] = [_THISROW].[project], [service] = [_THISROW].[service])
 )
)

 to show the action icon only for the rows whose entries have not been mande in the report table ensuring uniqueness.

I would show the action icon as inline so the entries in the report table can be made quickly.

[TeeSee1]

If I understand you correctly, you can try 

1) Create two slices for DEPLOYMENT, say, Services and Products

the filter expressions are simply ISNOTBLANK([FKID_SERVICE_DEPL]) and ISNOTBLANK([FKID_PRODUCT_DEPL]) respectively

2) Created two views on the slices with only those columns you want

3) If you want to see both views on one screen, create a dashboard to display both.

[MaurySj]

Hi TeeSee1, thanks for replying!!

Yes, with that I get the two lists.But... I would be missing the complicated part:

Sum the products or services that appear more than once.

Since only expressions that give a Yes/No result can be used in a segment, it is a bit limiting. 😑

Can you think of any way to do it? I already have my head burned from thinking so much XD 🤔😂

[TeeSee1 · Accepted solution]

The easiest way if you are not picky with your display format, then, you can create two virtual columns that concatenate (PROJECT & SERVICE) and (PROJECT & PRODUCT).

You can then configure your views to group on these VC's with GROUP AGGREGATE on [Quantity].

If you want a tabular format, then you need to create tables to extract unique combinations of  (PROJECT & SERVICE) and (PROJECT & PRODUCT). Then create VCs with SUM(SELECT(here get [Quantity] with Filtered by  (PROJECT & SERVICE) )).

Maybe other community experts can propose better solutions..

[MaurySj]

In addition to viewing the data in the app, I use it to generate a PDF that I then send to the different providers. So the first option doesn't work for me.

But the second looks very promising. It is clear to me how to get the sum.
But not how to get the tables with the unique combinations.
I would have to do this in Google Sheet and not in AppSheet?

[TeeSee1 · Accepted solution]

You can write an action, add a new row to another table using values from this row.

In the "Only if the condition is true", set something like

ISBLANK(
 FILTER("report table",
  AND([project] = [_THISROW].[project], [service] = [_THISROW].[service])
 )
)

 to show the action icon only for the rows whose entries have not been mande in the report table ensuring uniqueness.

I would show the action icon as inline so the entries in the report table can be made quickly.

[MaurySj]

Hi @TeeSee1!! Excellent! Thanks so much for the help and ideas! Your expression works wonders.

In the end I will end up doing it with a Bot, to make sure that there are no products or services left unloaded. But use the action to bulk load what you already had.

I would have liked to be able to achieve it without having to add new tables (because the app already has a lot hehe) but well it works, and with that I am happy hehe 😊

Again, thank you very much!!! 👍

[TeeSee1]

I was thinking of just adding a vc with SUM in the DEPLOYMENT and somehow extract unique (PROJECT & SERVICE) and (PROJECT & PRODUCT) combo rows in a report but could not come up with a good way.

Maybe someone can suggest something.

Well, if data volume is your concert, you can delete (archive) rows no longer required from the report table (and transaction tables) as your use cases allow.

[TeeSee1 · Accepted solution]

An idea just hit me.

Create a vc column with

[_THISROW] =
MINROW("Deployment", "_RowNumber",
 AND(
 [project] = [_THISROW].[project],
 [service] = [_THISROW].[service]
 )
)

which marks only one row having the min [_RowNumber] among the rows with the same project & service combination.

You can use this along with a vc with SUM to create a slice to get what you want.

Too many vc's can hurt performance and you should weigh it against having another table.

[MaurySj]

WOW!!! Amazing! In the end you got it!

These days I am busy, for work, as soon as I can try it!!

Too many vc's can hurt performance and you should weigh it against having another table.

---

That gives me another doubt. What most affects performance in AppSheet, many tables or many VCs?

I had already consulted in another Post about the number of tables.

Thank you again for your time!!!!

[MaurySj]

Hi @TeeSee1!!!!

Just today you can test your idea! It also works great!

Thank you very much for your help and your time! Communities like this are what make platforms really valuable!
A hug and we read somewhere!!!! 🖖

[MaurySj]

😯thank you for continuing to weigh in on this even though we already had a solution!!

---

@TeeSee1 wrote:

Maybe someone can suggest something.

---

It seems that no one else is interested...  but with your brain it seems that it is enough hehe.  🤣🤣

---

@TeeSee1 wrote:

Well, if data volume is your concert, you can delete (archive) rows no longer required from the report table (and transaction tables) as your use cases allow.

---

Yes, I had thought about doing some of that with the finished projects. But I think that for the moment it is not necessary.