Lookup on different Tables and many varying condit...

Ramitch_Palmare · 09-07-2023 10:12 AM

Hi guys!

I'm really at a loss here..

I have a client who operates a marketing business selling goods in wholesale..

I have an INVOICE TABLE wherein [PRODUCT ID]s and [FINAL CASE PRICE] are entered

I also have a Master File Table where [PRODUCT ID]'s are listed and the corresponding [CASE PRICE] as well. This table is what I call the Regular Table...

Most of the time, the regular [CASE PRICE]s are followed or used in the Invoice Table row unless there are negotiations between [CUSTOMER] and the owner of the company.

All negotiations are listed in "Blueprint" Table wherein the following columns are important and are needed to reflect the [NEGOTIATED PRICE] OR [NEGOTIATED DISCOUNT PERCENTAGE]

[CUSTOMER]
[AGENT]
[PRODUCT]
[NEGOTIATED PRICE]
[NEGOTIATED DISCOUNT PERCENTAGE]
[SUPPLIER]

If numbers 1, 2, AND 3 are matched, the [NEGOTIATED PRICE] SHOULD REPLACE [FINAL CASE PRICE] in the Invoice Table Row instead of [CASE PRICE] under the Master File Table,

If false, another check should be made if numbers 1, and 2 only are matched. If so, the [NEGOTIATED PRICE] shall also replace the [FINAL CASE PRICE] but using [AGENT] and [ PRODUCT] as conditions only.

I have attached my desired flowchart and I hope you can help me or share some ideas for this concept. Thanks!

jaichith

Actually what is your expectations ? Do you need expression? Technique to solve ?

You have completed every thing on your side. Amazing ..... clear Understanding of the problem is equal to problem is half solved.

Please state your requirements .

Ramitch_Palmare

Oh I'm sorry. Yeah, I need to understant the best expression to change the [FINAL CASE PRICE] according to the varying conditions on the flowchart... without undermining very much the performance of the application...

jaichith

It is basically IF() condition "if (condition , true, false)" . Another option IFS available . You should use lookup or ( filter or select function with Any () ) to pull the values based on the condition.

jaichith

Redraw the flow chart in given yes or no format , so that I can help you in constructing expressions

https://www.google.com/search?q=flowchart+symbol+for+decision+making&tbm=isch&ved=2ahUKEwjSjcu8kZqBA...

jaichith

This functions will help you

IFS

IF

Select

Filter

Lookup

Any

AND

Or

ISblank

Isnotblank

_This row

_This

In

Properties like

1. Initial Value

2. Formula etc

Check and learn all these above functions you can complete on your own. If you need help let us know.

bmpoweravn

Hello @Ramitch_Palmare Kailangan mo pa ng tulong dito?

Lookup on different Tables and many varying conditions...