Using Calculated/Look ups with conditions

There might be a different way of doing this (thank you Microsoft for offering 100 different ways to do a thing 😄) 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!