Portals and related fields
I've got quite a basic problem (I think) that I hope someone can help with.
I've got a database consisting of a table of Stock and a table of Orders. These tables are related so that when you create a new Orders record, you select an item from a dropdown box based on the Stock table, and several other fields related to the selected item are then displayed from the Stock table. Among others, the Orders table has its own field called Order Status, which can be Pending, Backorder or Delivered.
My problem has arisen because I wanted to create a layout where I could display a list of the different types of orders depending on their Order Status. The only way I could work out how to do this was to create a new table with just one field, Order Status, relate that field to the Orders table, and then create a portal that displayed the info from the Orders table that way. I was hoping to do this via a layout, but there didn't seem to be any way to get the layout to only display records based on data in a specific field (if anyone knows how to set this up, I'd be grateful).
My problem with the portal approach is that the portal in the third table displays the data in the tables that are "native" to the Orders table just fine, but the fields in the Orders table that are populated based on data in the Stock table just come up blank in the portal. Why? I understand that the third table and the Stock table aren't directly related, but I don't see how they can be since they have no fields in common, only the ones in the portal, which I can't base a relationship on. Is a portal not able to display data that is once removed, so to speak?
I hope the explanation above makes sense. Thanks for reading.