1. Filtered portal
Create a global date field in the Product table; use it to filter a Balance portal with
IsEmpty ( Product::gFilterDate ) or Product::gFilterDate ≤ Balance::Generation_date
Make sure you add some kind of field trigger to refresh the display.
Depending on the number of related Balance records per product, and/or your further plans in using (and not just displaying) the related values, this may not be a solution for you; in that case …
2. Filter by relationship
Again, create a global date field; give it an auto-enter calculation of
Case ( IsEmpty ( self ) ; Min ( Balance::Generation_date ) ; self )
Create a new table occurrence of Balance, say, Balance_filteredByDate, and use it to create a new relationship:
Product::Product_pk = Balance_filteredByDate::Product_fk
Product::gFilterDate ≤ Balance_filteredByDate::Generation_date
Place the global field and a portal into that new relationship on your Product layout, and use it …
I want to see only changes of stock that was taken since some date […]
when I add extra filed to relational between PRODUCT TABLE and BALANCE TABLE I can see only part of records but Calculation also change and dosen't show real value.
Well, that's the effect you're after, right? Filtering the relationship (i.e. adding another predicate) will result in a smaller subset than an unfiltered relationship (fewer predicates). Not sure what you mean by “real value”.
HOW TO DO THAT??
Please be so kind and don't use ALLCAPS and/or excessive punctuation!!!!
Most users in this forum are neither deaf nor blind … so that wouldn't usually be necessary
Hi thanks for fast reply.
I did what you advice but this is not working. I think it will be more clear if I add example:
Look at Stock change in excel. Everything is explained there.
Calculation change when I make filtered by relationship and I would like to see real value.
I'm afraid that will not work If you use summary fields for sum_of_in ( running ) and sum_of_out ( running ).
As soon as you limit your data set by date the summaries will re-count from the first record shown upwards.
So it will not show correctly how much total inventory was on that date, just how much you added or removed.
If you want to see how much inventory was on a given date the table should look more like this:
These are all stored numbers so you create scripts that create a record in this table every time there's a change to inventory.
The tricky bit is ( when you make the change ) how to calculate the new Stock Total as you will need the last record.
This can be done by having a relationship to the Product sorted by ID ( descending ).
I hope i'm not making it overly complicated.