Hi all. I have a tabular form based on “Sales Invoice Line” table, where I need to see all the lines over a period. That period is dependent on the “Posting Date” of the “Sales Invoice Header” . So I need to filter the “lines” table based on a field of the “header” table. In SQL it would be something like: SELECT * FROM “Sales Invoice Line” WHERE EXISTS ( SELECT 1 FROM “Sales Invoice Header” WHERE “Sales Invoice Line”.“Document No.” = “Sales Invoice Header”.“No.” AND “Sales Invoice Header”.“Posting Date” = TODAY); I don’t think I can use a filter to do this, but I’m a novice and any help’ll be much appreciated. Regards, Henrique
Henrique, you can use filters for everything [:D] I would suggest adding a FlowField to the “Sales Invoice Line” table, with a CalcFormula that does a Lookup into the “Sales Invoice Header” table, looking up the “Posting Date”. You can then set a simple filter on this FlowField in your form.
Heinz, It worked perfectly (a little slow, maybe, but it works). Thanks, Henrique
Henrique, If this is an important requirement and the number of records is expected to grow a lot, you could approach the situation a little differently: The use of the FlowField is what slows it down. You could convert the new field Heinz suggested - “Posting Date” in the “Sales Invoice Line” table - to a Normal field and fill it in during Posting. If you feel that you need it, this will make it work much faster.