Relationship Problem

[James Patrick]

Hello,

If anyone can point me in the right direction with this it would be greatly appreciated. Basically I have 3 tables: clients, suppliers and transport orders and what I want do is have a drop down list on the transport order layout that shows a list combining client names and supplier names. It is not important that the list shows whether the person is a client or supplier it just needs to show the names.

I have been trying to work out the relationship but with no success.

Thanks

James

[Vaughan]

This cannot be done because the clients and suppliers are in different tables.

Instead, consider storing clients and suppliers in the same table, with a "type" field to mark them client or supplier.

[James Patrick]

Hi Vaughan,

Thanks for the reply, you've saved me a lot of time in experimenting.

Thanks again

James

[James Patrick]

Hello again Vaughan,

Just thinking about this, if I have 2 layouts based on the same table, 1 layout for clients and 1 for suppliers can I still differentiate between the 2.

eg I will need to have a drop down list for suppliers on a page, on another page I need a drop down for clients and on another for both.

Is this still possible?

Thanks

James

[comment]

Please update your profile to reflect your version.

[James Patrick]

Hi Comment,

I have updated my profile, I am using version 11 advanced.

Thanks

James

[comment]

if I have 2 layouts based on the same table, 1 layout for clients and 1 for suppliers can I still differentiate between the 2.

A layout can show any/all records from the associated table. To show only records of a certain type, you must perform a find. You can set the layout to do this automatically by using a OnLayoutEnter script trigger.

I will need to have a drop down list for suppliers on a page, on another page I need a drop down for clients and on another for both.

This has nothing to do with layouts - it's a question of defining three value lists. Two of these need to be conditional (i.e. filtered by a condition). This can be done by defining a relationship and setting the value list to show only related records.

[James Patrick]

Thanks Comment, this has given me a lot to think about.

James

[James Patrick]

Hello,

I have been playing around with the idea of having 2 layouts clients and suppliers, based on one table. On each layout is a field "type" where you choose supplier or client and when you enter each respective layout a script is triggered that performs a find based on the layout to only show records of that type.

I have a couple of questions as this is a bit new to me

Is it possible to automatically fill in the field "type" on each layout when you start a new record?

when you are in on the supplier layout for example, the found set says 3/5 where 3 is the found set of supplier records and 2 is the client record. Is it possible for this to say 3/3 as I think it might be confusing for the user?

Will this approach slow the database down? If there are a lot of records, will there be a delay performing the find everytime you open the layout?

Any help would be greatly appreciated

Thanks

James

[comment]

On each layout is a field "type" where you choose supplier or client

This is not necessary. You either need dedicated layouts (because the fields shown for each type are different) or a single layout with a choice.

Is it possible to automatically fill in the field "type" on each layout when you start a new record?

Yes, you can set the field to auto-enter a calculated value based on the layout's name.

the found set says 3/5 where 3 is the found set of supplier records and 2 is the client record. Is it possible for this to say 3/3

No, but you can hide the status area and show your own message.

Will this approach slow the database down?

I don't think so - a find on an indexed field is very fast.

[James Patrick]

Thanks for the reply comment, you have given me lots to think about.

James