Good morning everyone.
I need help to solve this problem:
I have a table (Table 1) composed by these column
Code1 | Starting Date | Ending Date | Description | Total Ordered Qty.
Total Ordered Qty is a flowfield and the actual CalcFormula is
Sum(“Table2”.Quantity WHERE (Nr.=FIELD(Code1))).
I need to add also a datefilter for “Starting Date”…“Ending Date” so i can see the Ordered Qty by Code and by Period (in Table2 i have a “Order Date” field and i want to use this) .
Now, how can i add a filter like that in Table1?
As far as i know i cannot add it in the flowfield calcformula but I didn’t find a way to add a correct SETRANGE.
SETRANGE(“Table2”.“Order Date”, Starting Date, Ending Date ) doesn’t work because it’s wrong.
Probably there’s a very simple solution for this (so please forgive me) but i’m not able to find it and it’s driving me crazy.
Thank you in advance
Check the G/L Account (T15). You will find a field ‘Date Filter’ (field 28) which is a FlowFilter, and go through the ‘Table Filter’ property of field ‘Net Change’ (field 32). I hope this will give you the pointers on how to do this. You will have to change the CalcFormula to add the flowfilter fields there. You will most probably not require to use the ‘Starting Date’ and ‘Ending Date’ fields from Table 1, what you would need is to create a ‘Date Filter’ in Table 1 and I suppose that you will have a some date field already in the Table 2, which you will have to use in the Table Filter property of the Total Ordered Qty in the formula definition, as it is done in the G/L Account (considering G/L Account as Table 1 and G/L Entry as Table 2)
Hello Roy, thanks for your reply.
The problem is that my filter is different from “Date Filter” in table 15 because here you can apply a date range for all the records in table 15 but I can’t do the same in my table.
A quick example:
table 15: here you apply a date filter (01/01/2013…01/31/2013) for all G/L Accounts
my table 1: is different because I have a situation like that
1|Code1 | St.Date 01/01/13 | En.date 01/31/13 | Description | Total Ordered Qty.
2|Code1 | St.Date 02/01/13 | En.date 02/28/13 | Description | Total Ordered Qty.
3|Code1 | St.Date 03/01/13 | En.date 03/31/13 | Description | Total Ordered Qty.
…
So the point is that i can’t apply the same date filter for all the records; each line needs a different date filter.
I hope is clear now.
I am not sure that I understand your problem yet. However, after going through your response, a couple questions that come to mind are:
-
Does this ‘Code1’ have another master table? (If yes, can we do this ‘Date Filter’ stuff on that top level master table?)
-
Table 2, as I understand must have a date (posting date or transaction date sort of), so, what is the need of this intermediate Table 1?
Could you clear my doubts please? [:(]
Kind regards,
Roy
Hello Roy, thanks for your time.
-
Yes, Code1 have a master table but No, i can’t do this date filter on that table.
-
Table 2 have a date (order date) and it is table 39. Table 1 is a different table that regroup (per Item, per Starting Date/Ending Date) different data from different table: in one field it shows Ordered Qty, in another field shows Sold Qty (from table 37), etc…
Each record in Table 1 have a different Starting Date and Ending Date (as my example in previous post) so i cannot apply a global Filter Date for all records, i must apply a different date filter for each record line in Table 1.
Always form my previous example:
1|Code1 | St.Date 01/01/13 | En.date 01/31/13 | Description | Total Ordered Qty. -----> i need to show Ordered Qty. filtered for date range 01/01/13…01/31/13
2|Code1 | St.Date 02/01/13 | En.date 02/28/13 | Description | Total Ordered Qty. -----> i need to show Ordered Qty. filtered for date range 02/01/13…02/28/13
3|Code1 | St.Date 03/01/13 | En.date 03/31/13 | Description | Total Ordered Qty. -----> i need to show Ordered Qty. filtered for date range 03/01/13…03/31/13
Hi Marco,
I got your problem now. Two methods of doing this is coming to my mind now.
-
You may need to have these fields updated by some batch process (very crude way)
-
FlowField in the following manner:
Let’s say you have 3 FlowFields against each set of Code,Starting Date, and Ending Date, namely, Total Order Qty, Total Shipped Qty and Total Invoiced Qty. Now you can have a table like
Qty_Type - Option - Ordered,Shipped,Invoiced - Store the type of Qty to store in this record
Code - store codes
Starting Date - store starting date
Ending Date - store ending date
Total Qty - store the ordered, shipped or invoiced qty based on the Qty_Type.
This above table enables you to make the flowfield Total Qty use a filter on the starting and ending dates as well as the qty_type individually from the Table 2.
Does this make sense?
Kind regards,
Roy
I think you guys are confused total orderd qty will be calculated for order date in table 2 not on starting date and ending date in table 1.
so you create a date filter… and automatically u will get the total orderd quantity with range…
AND IN THE TOTAL ORDERED QTY FORMULA FILTER IT WITH THE DATE FILTER
Not at all Anil.
You didn’t understand my problem but, in the end, i found a way.
I numbered the week in my table 1 (like 01-01-13…01-06-13 = 1, 01-07-13…01-13-13 =2,etc…) and i did the same with the records in table 2 (if posting date is between 01-01-13…01-06-13 = 1,etc…) and then i applied the filter for this new field.
I repeat it’s impossible use a filter date in that case.
Thanks anyway for your time.
Regards