Question about listing multiple records in a portal using FM Pro 8.5
I am modifying an old FM system designed with FM 3. We have it imported in FM Pro 8 and everything is working fine but unfortunately there are several DB files instead of one, since version 3 didn't allow multiple tables in one DB file like versions 7 + do.
I will give a little explanation of how things work (in our system) so you can understand my needs:
We have a DB called "orders". Orders has many layouts such as invoice, work order, pull sheet etc. There are also two different Purchase Order Layouts, with unique fields in each - let's call them PO1 and PO2. Right now we also have a Due Date DB that lists days of the current week. When you click "Monday" (for example) it goes to another layout in the (due date DB) that has a portal that lists all the records from the orders db who's PO1 have a due date of that current week's Monday. Now, what I would like to do is have it list all the records from the order db who's PO1 and/or PO2 have that specific due date. I was able to work it out in relationships to list records who's PO1 AND PO2 have that specific due date, BUT "and" is the word that is limiting me here. Let's say there are two records order1 and order2) Order1 has PO1 with the due date of 11/17/2009 and PO2 with the due date of 11/20/2009. Order2 has PO1 with the due date of 11/18/2009 and PO2 with the due date of 11/17/2009. In the due date DB when you click "Tuesday 11/17/2009" I need it to list both order1 and order2.
Can anyone tell me how to achieve this? It seems that when defining relationships, an "OR" option would do the trick, but unfortunately I don't see one :(
I hope my explanation above makes sense.
Please let me know if you need any clarification.
Thanks very much!
Define a new calculation field in the same table as your purchase orders layout:
PO1DueDate & "¶" & PO2DueDate
Link your relationship to this one field instead of the two PO due date fields and the records will match if PO1DueDate OR PO2DueDate matches the date in the other table.