What's more，how can it just list the products that the period of delivery or warehouse quantity > 0 after search?
It's not entirely clear to me what you are asking, but from your second post perhaps it is a Find issue. To find only the records with a delivery date within a specified period and a quantity of > 0, just enter both criteria in Find Mode: a date range (e.g. 1/6/15 … 30/6/15) in the delivery date field and >0 in the quantity field. That is an AND search, and will result in only those records that meet BOTH criteria. It may even be that the quantity criterion is not needed: surely if a delivery is recorded there must be a quantity?
Is there anyone who can help me?
There is a question that how can I get the total quantity of product (a date range (e.g. 1/6/15 … 30/6/15) ) in the delivery and warehouse?
If you find the records in the date range required, then use a summary field (Total of product_inventory_quantity) in a leading or trailing grand summary part, that will give you the answer you are looking for.
To get better answers, you might need to ask better questions with say an example of what you are expecting.
For example, I want to get that (in a date range (e.g. 1/6/15 … 30/6/15) ) which product has been out of storage and put in storage(contains their quantity each other).
And get the storage quantity of these product before 1/6/15, and the storage quantity of these product at 30/6/15.
How can you tell "which product has been out of storage" by looking at the data?
I realise English may not be your first language, but I am struggling to understand what you are asking.
Can you provide some data in a post here and the answers you might expect from that data?
For example, given a set of records, how would you manually calculate "the storage quantity of these product before 1/6/15"?
I'm a Chinese that my English is poor, so something I couldn't explain clearly.
I'm so sorry about that.
I have upload the file
You can download it
Yes I have downloaded the file and had a look at it.
However, it does not really help me understand what you are trying to show.
Which layouts should I be looking at? Which data?
What would you like the database to calculate and display for you based on data that is in there?
I want to display the
in layout Products
after I click the Search button and choose the date range, press the checkFind button.
image.jpg 259.2 K
For example, the "product_delivery_quantity" is the total product_quantity of DeliveryLineItems in a date range,
the "product_warehouse_quantity" is the total product_quantity of WarehouseLineItems in a date range.
(If the value of product_quantity of DeliveryLineItems of Delivery in date 3/6/15 is 3, and in date 12/6/15 is 2,
20/6/15 is 4, so the "product_delivery_quantity" should be (3+2+4) in date range 3/6/15 … 20/6/15)
If the product_inventory_quantity of product_number ( CP0001) is 10 at 31/5/15 (the date range (e.g. 1/6/15 … 30/6/15)), so the value of "product_inventory_quantity_before_search_period" should be 10.
And if the value of "product_delivery_quantity" is 5 and the value of "product_warehouse_quantity" is 20,
then the value of "product_inventory_quantity_after_search_period" should be 25.
for "classical" FileMaker technic you'll have to build relations on the graph from your search date fields to the tables containing the data to be summarized, then you can do Sum ( TheRelationBasedOnDates::theDataField )
you can do this with ExecuteSQL without additional relations on the graph.
Your calculated field for product_delivery_quantity could look like this:
ExecuteSQL ( "
SELECT SUM(dli.product_quantity) FROM
DeliveryLineItems dli JOIN Delivery d ON dli.ID_Delivery=d.ID_Delivery
WHERE dli.ID_Product=? AND d.\"Date\" BETWEEN ? AND ?
" ; "" ; "" ; Products::ID_Product ; Products::product_search_start_date ; Products::product_search_end_date
Thanks so much. That's right.