Join info from two files

[ak98]

I have two simple files: fileA is an order form for customers. FileA has a portal to fileB where are line items with: goods and quantity fields.

I have related the files by using the orderID field on both files.

How do I make a report which will give me the summaries of the qunatities in FileB inbetween cerain dated of order. the date field is part of FileA.

Thanks for answering this simple question.

[Ugo DI LUCA]

If the date your are looking to perform a find on are the Order date, then simply set a new field in your line item that would be a lookup from the Order file based on relationship Order_ID::Order_ID

[Razumovsky]

An easy way to do it is in File A to have 2 global date fields g_Datefind1 and g_Datefind2, and a calc field c_DatefindText (result is text):date to text (g_Datefind1)&"..."&date to text(g_Datefind2).

In file B, you can just place the related Orderdate field on a layout using Ugo's relationship FileB:OrderID::OrderID:FileA, or to speed up the find process, you can define a OrderDate field in FileB that will autoenter the date from the related FileA record on creation (assuming that the Order Date in FileA is entered first before any records in the portal, and will never be modified).

Have the user enter the begin date in g_datefind1 and end date in g_datefind2.

You than write a script that copies c_datefindText from File A and performs script in File B that enters find mode and pastes the datefind text into the orderdate field in FileB, performs find, sorts, goes to summary layout (still in fileB), Prints or eneter preview mode/pause, returns to original layout in FileA.

If you are comfortable with relationships, you can create an all to all relationship between FileB and FileA, and use set Field(FileBOrderDate) to AlltoAll::DatefindText. This will be in place of the copy/paste part.

The short answer to your question is that the summary layout will be created in FileB.

HTH

[danjacoby]

You need a relationship from fileB to fileA based on the OrderID.

Then create a calc field that returns a date -- it equals the related date from the order in fileA.

Now in fileB you can search for a date range and create a report that shows the various totals.