Filtering subtables

Hi Charlotte -

It can help if we have a little better understanding of the relationships of the tables in question. What table are you in and what kind of relations (1:1 or 1:N) to the Shipping-Cost table does it have?

Thanks,

Ok, just a few more questions.

1) The first screenshot is of a record in your Inventory table? Or is it a dashboard?

2) In the first screenshot is that Tarifs table a view? If so then what table are you looking into?

3) You say, "In the inventory I have a shipping-weight field that I would like to use to get the shipping prices for every zone.". My question, maybe I'm not thinking clearly, is why would you want all of your shipping prices for all zones into one field? Wouldn't the shipping-weight field be a simple number (1kg, 100kg, etc)? Also why would you want to see shipping prices in Inventory? Wouldn't you want to see it in Invoicing or wherever you figure out pricing to charge someone?

Very helpful info.

As with any database, more questions.

1) Is a single shipping cost really related to a single record in your inventory? Doesn't a record in your inventory have many shipping cost options depending on zone. Does each record in Inventory exist one time and when sold becomes obsolete?

2) I can't get into the web side of things, but it seems like you need an Order or Quote table that gets the item from Inventory, gets the shipping address which then figures out which Zone from your destination table and then figures out the shipping cost based on the weight of the item. Then the Order or Quote either gets deleted or becomes an Invoice. If you don't have a lot people getting only quotes (say less than 10%) then maybe you can just use the Invoice table with a field that marks it as a quote or something like that.

Again, just my 2 cents worth of blabber. Maybe I'm missing something and overthinking it.

Just a hint without analysing the complete database structure. In your formula, what does "Product-shipping-weight" stand for? If it is the name of a field from another table than the one linked by "Shipping-Cost.Price-DHL", the formula won't work. The reason is that the condition is evaluated in the context of, in turn, each record of the linked table. Fields from any other table are out of reach. So, if you need to compare a field of the linked table with a field from any other table, you must save the value of that field in a variable before performing the filtering, and use the variable in the condition.