If Sales table contains a certain job, then mark Company in another table "Yes"

[PeterStuhr]

Hello

In my company we have a "Company Info" table and a "Sales" table. Each row of the Sales table is an order line containing Company ID, Job type, revenue and date.

We want to track which companies are buying which jobs.

Example of tables:

Company Table:

Company ID
10001
10002
10003
10004
10005
10006
10007
10008
10009
10010
10011
10012
10013
10014
10015
10016
10017

Sales Table:

Company ID	Job Type	Revenue	Date
10001	A	100	10-08-2019
10001	A	100	10-08-2019
10001	A	100	10-08-2019
10001	B	100	10-08-2019
10001	B	100	10-08-2019
10001	C	100	10-08-2019
10001	C	100	10-08-2019
10001	D	100	10-08-2019
10001	D	100	10-08-2019
10001	E	100	10-08-2019
10001	E	100	10-08-2019
10001	F	100	10-08-2019
10001	F	100	10-08-2019
10001	A	100	10-08-2019
10001	A	100	10-08-2019
10001	A	100	10-08-2019
10001	B	100	10-08-2019
10001	B	100	10-08-2019
10001	C	100	10-08-2019
10001	C	100	10-08-2019
10001	D	100	10-08-2019
10001	D	100	10-08-2019
10001	E	100	10-08-2019
10001	E	100	10-08-2019
10001	F	100	10-08-2019
10001	F	100	10-08-2019
10001	A	100	10-08-2019

For instance, Job type "F" is important for us to track, so if just ANY row in the sales table is the Job Type "F", then mark the Company table with "F"=yes.

So ideally input, since Company 10001 is buying Job type F, the company table should say:

Company ID	F Job
10001	Yes

Possible?

[Mariusz · Accepted solution]

Hi @PeterStuhr 

Of course, it's possible, please find the attached file with the solution included.

You will be able to use the UI for most of the steps, but some manula M interference will be necessary.

In the file, you will see two tables that I have a reference to the Sales table, one contaning original code built with UI and second with manually adjusted M code, I have marked the steps in this table so it's easier to see where you will need to make the adjustments.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.