I have a database that consists of the following tables.

Orders = contains list of orders, there is only one entry per order
OrdersExp = contains an expanded view of each order. Every order we
have has at a minimum two entries in this table, one for the customer
and the other for the supplier piece. Some orders have deliveries that
are met monthly so if we have a 12 month order (delivering a product
once a month for twelve months) then you will find 12 entries for the
customer piece and 12 entries for the supplier piece in this table, all
with the same matching orderID from Orders.

What I want to do is have a confirmation form where the user enters via
find the OrderID and the form will pull the details from Orders, but
then it needs to pull the individual entries from OrdersExp and show
each delivery month and the quantity that will be delivered.

I'm fairly new at FM and I'm pretty sure that 2nd piece requires a
portal. My big question is, how do I limit what it pulls in that
portal to only the customer records. Right now it pulls both customer
and supplier records because all of them have the same order ID that
you find in Orders. There is a field in each record in OrdersExp that
tells you whether it is a customer or supplier record. I just don't
know how to make the portal pull only those records marked as customer
records.

In article <2007072715182216807-dougcpa [at] maccom> Doug
Anderson<dougcpa [at] mac.com> wrote:
> I have a database that consists of the following tables.

> Orders = contains list of orders, there is only one entry per order
> OrdersExp = contains an expanded view of each order. Every order we
> have has at a minimum two entries in this table, one for the customer
> and the other for the supplier piece. Some orders have deliveries
> that are met monthly so if we have a 12 month order (delivering a
> product once a month for twelve months) then you will find 12 entries
> for the customer piece and 12 entries for the supplier piece in this
> table, all with the same matching orderID from Orders.

> What I want to do is have a confirmation form where the user enters
> via find the OrderID and the form will pull the details from Orders,
> but then it needs to pull the individual entries from OrdersExp and
> show each delivery month and the quantity that will be delivered.

> I'm fairly new at FM and I'm pretty sure that 2nd piece requires a
> portal. My big question is, how do I limit what it pulls in that
> portal to only the customer records. Right now it pulls both
> customer and supplier records because all of them have the same order
> ID that you find in Orders. There is a field in each record in
> OrdersExp that tells you whether it is a customer or supplier record.
> I just don't know how to make the portal pull only those records
> marked as customer records.

The first thing I would do is make Customers and Suppliers separate
tables. They can still be based on the same order number as ID, but
there would be no confusion from combining two separate entities in
one table. You wouldn't need a marker to identify a customer record.

As it stands, though, make the relationship between Orders and
OrdersExp match both the Customer ID and the field that identifies the
record as that of a customer. You would need a global field in the
Orders table with that customer marker value in it in order to satisfy
the relationship (something else that would not be necessary with
separate Customer and Supplier tables).

To do the same thing with Suppliers, you would have to create a new
Table Occurrence with the records from OrdersExp, only this time match
the order number and NOT match the customer marker.

Matt

--
Free FileMaker Technique Demos: http://www.VirtualVermont.com/FMP

My Custom Functions: http://www.briandunning.com/filemaker-custom-functions/resul ts.php?keyword=wills

On 2007-07-27 20:00:51 -0500, Matt WIlls <Im [at] Witz.End> said:

> In article <2007072715182216807-dougcpa [at] maccom> Doug
> Anderson<dougcpa [at] mac.com> wrote:
>> I have a database that consists of the following tables.
>
>> Orders = contains list of orders, there is only one entry per order
>> OrdersExp = contains an expanded view of each order. Every order we
>> have has at a minimum two entries in this table, one for the customer
>> and the other for the supplier piece. Some orders have deliveries
>> that are met monthly so if we have a 12 month order (delivering a
>> product once a month for twelve months) then you will find 12 entries
>> for the customer piece and 12 entries for the supplier piece in this
>> table, all with the same matching orderID from Orders.
>
>> What I want to do is have a confirmation form where the user enters
>> via find the OrderID and the form will pull the details from Orders,
>> but then it needs to pull the individual entries from OrdersExp and
>> show each delivery month and the quantity that will be delivered.
>
>> I'm fairly new at FM and I'm pretty sure that 2nd piece requires a
>> portal. My big question is, how do I limit what it pulls in that
>> portal to only the customer records. Right now it pulls both
>> customer and supplier records because all of them have the same order
>> ID that you find in Orders. There is a field in each record in
>> OrdersExp that tells you whether it is a customer or supplier record.
>> I just don't know how to make the portal pull only those records
>> marked as customer records.
>
> The first thing I would do is make Customers and Suppliers separate
> tables. They can still be based on the same order number as ID, but
> there would be no confusion from combining two separate entities in
> one table. You wouldn't need a marker to identify a customer record.
>
> As it stands, though, make the relationship between Orders and
> OrdersExp match both the Customer ID and the field that identifies the
> record as that of a customer. You would need a global field in the
> Orders table with that customer marker value in it in order to satisfy
> the relationship (something else that would not be necessary with
> separate Customer and Supplier tables).
>
> To do the same thing with Suppliers, you would have to create a new
> Table Occurrence with the records from OrdersExp, only this time match
> the order number and NOT match the customer marker.
>
> Matt

Unfortunately the customer & supplier tables are part of a corporate MS
SQL database for which I cannot fiddle with the structure. But after
reading your 2nd paragraph I started reviewing my tables again and
found a way to link my two tables through a 3rd table to get it to pull
only the customer records.