Solved: sum+select+and Formula

AndreaSangalli · 10-17-2022 08:58 AM

Hello There,

I'm trying to figure out how to fix this formula.

I have a TBL call ITEMS where where are 3 column

Company

Products Name

Unit Price

in this TBL the SAME prodocuts name can have different price for different company

In another TBL call INVOICE LIST OF ITEMS I have these column

UniqueID

InvoiceID

Products

Company

Quantity

Price

Lastly the INVOICE TBL

Company

Address

Ref

Phone

InvoceID

Date

Email Invoice?

Note

Total

My goal is when I'm selected the a company (in INVOICE TBL) the app give the exact amount for the Company

With this formula in the TBL INVOICE LIST OF ITEMS in PRICE the app is give me 0$.

Sum(select(Items[Unit Price],and([Products]=[_Thisrow].[Products].[Products Name],[Products]=[_thisrow].[Products].[Unit Price])))

I had tried different way but I can't resolve this dilemma

Thanks in Advance

WillowMobileSys

In your Items table, you have Products Name as the key. It cannot be the key if the same Product can appear for different Companies. You need to have a separate key column (AND I don't recommend to use a "name" column as a key). I would suggest simply including a UNIQUEID() column named maybe Item ID or Product ID.

The middle table looks to be the Invoice table? Here you have Company as the key column. InvoiceID should probably be the key column.

Once you have these columns set to the appropriate Key columns, when you select Product for your "Invoice List of Items" table, you should assign the Item ID or Product ID into the Products column. Then to assign Price in the "Invoice List of Items" table, you only need to use "dot" notation to "de-reference" your Products Ref column....like this:

[Products].[Unit Price]

Try the above to see how far you get and come back here to ask questions.

It might also be helpful to get a better understanding of table relationships. I would recommend looking over this article: References between Tables

Pay close attention to this section "Express ownership between tables".

View solution in original post

WillowMobileSys

In your Items table, you have Products Name as the key. It cannot be the key if the same Product can appear for different Companies. You need to have a separate key column (AND I don't recommend to use a "name" column as a key). I would suggest simply including a UNIQUEID() column named maybe Item ID or Product ID.

The middle table looks to be the Invoice table? Here you have Company as the key column. InvoiceID should probably be the key column.

Once you have these columns set to the appropriate Key columns, when you select Product for your "Invoice List of Items" table, you should assign the Item ID or Product ID into the Products column. Then to assign Price in the "Invoice List of Items" table, you only need to use "dot" notation to "de-reference" your Products Ref column....like this:

[Products].[Unit Price]

Try the above to see how far you get and come back here to ask questions.

It might also be helpful to get a better understanding of table relationships. I would recommend looking over this article: References between Tables

Pay close attention to this section "Express ownership between tables".

AndreaSangalli

I really appreciate your help.

I tried what you suggested.

Unfortunately the App still gives me only one price.