Using Calculated/Look ups with conditions

[GaryWilliams]

Excuse my lack of knowledge but my past life I was developer but on relational dbs.

So I get lists are very Excel oriented, but I'm trying to put some relations between my lists as like Db Tables and wondering how best to do this.

As a simple example ... I have 2 lists Client Projects and Project Orders, so when I raise a Project Order I only want to see the "Active" Client Projects.  I done this by setting up a new column on the Project Order that calculated if the Client Projects was "Active".  Worked great when adding a new item, and I saw the Client Project Name however when the Client Project was "Closed", in the list view the Client Project Name then disappeared.  Which obviously I know why as that Column only gives that information when "Active".

So I know with my background I can do this somehow, but how is the best way?

[#2]

There might be a different way of doing this (thank you Microsoft for offering 100 different ways to do a thing :D) but personally I would look to Power Automate to work in concert with your lists. You would create Flows where all your triggers would be 'when a new list item is created' OR 'list item is updated' (or deleted). Then, for example, when an order is added (no matter how you add the order info to the list), you write the flow to find the corresponding client info on the client list (have it search for the matching values like the name or id#) and then have it add the info you want from the client list, like client ID, or name, to the order item, maybe even auto add order status as Active, and vice versa, so the order # and status of order gets added to the client list item. You can set it so if it doesn't find a match, you could tell it to add something like 'add new client' (or get crazy and have it auto add a new client!) If you go this route, you will want to create the columns for the' new' relational info you want to see before hand, so PA knows where to put the selected info, and write the flows in Automate and your tables are effectively tied together. You could even have set it up so PA sends you an email or notification every time a new item is added to your list, or maybe when an order status is changed to help you manage/follow the changes when they happen. I hope this helps!

[#3]

Thanks

I think I understand you but, for me with my background you are effectively updating the item twice ... once to create it, being selective with the look up, and then secondly to overwrite without?

[#4]

Yeah, you could look at it that way. You are creating the entry, and using a flow to immediately 'update/modify' a targeted cell (or cells) on said entry. Explaining/setting up seem not so pretty, but in action it pretty straight forward/hands off.

[#5]

(to clarify, on the 'update' you aren't overwriting anything/any cells you don't want to update/overwrite... You would leave those cell refs in the flow blank and flow would ignore them and the original info from initial creation would remain in tact ).