Hi

I am a filemaker newbie working with version 6. I need to manage goods
ordered from different suppliers.

I have the following tables and fields:
- Table Goods with fields GoodID and GoodName
- Table Suppliers with fields SupplierID, SupplierName and more..
- Table GoodsSupplier with the fields SupplierID and GoodID as well as
GoodName and SupplierName, which are calculated fields to get the
values from the other two tables.

Now my problem: In a layout in the Goods table I have a portal showing
the suppliers for this good. I want to have a text field with a popup,
where all suppliers are displayed and the user can choose one, but in
the record I want to save the corresponding SupplierID. Right now I
defined a value list with the two fields SupplierID and SupplierName to
choose from. I don't want to display the ID, but only the name...

regards
Hans

In article <cqenij$cm8$1@news.hispeed.ch>, "Hans Hilper"
<hhn10@gmx.net> wrote:

> Hi
> 
> I am a filemaker newbie working with version 6. I need to manage goods
> ordered from different suppliers.
> 
> I have the following tables and fields:
> - Table Goods with fields GoodID and GoodName
> - Table Suppliers with fields SupplierID, SupplierName and more..
> - Table GoodsSupplier with the fields SupplierID and GoodID as well as
> GoodName and SupplierName, which are calculated fields to get the
> values from the other two tables.
> 
> Now my problem: In a layout in the Goods table I have a portal showing
> the suppliers for this good. I want to have a text field with a popup,
> where all suppliers are displayed and the user can choose one, but in
> the record I want to save the corresponding SupplierID. Right now I
> defined a value list with the two fields SupplierID and SupplierName to
> choose from. I don't want to display the ID, but only the name...
> 
> regards
> Hans

If I'm reading this correctly, all you need is a relationship link from
from the GoodsSuppliers table to the Suppliers table using the
SupplierName as the linking field,
ie. 
      SuppliersLink (in GoodsSuppliers)
      SupplierName = Suppliers::SupplierName

then the field SupplierID in the GoodsSuppliers table can be a
Calculation field retrieving th data via this new relationship,
ie.
      SupplierID {Calculation, Text Result, Unstored}
          = SuppliersLink::SupplierID


BUT, you say above that SupplierName in the GoodsSuppliers table is a
Calculated field, which it can't be since you're entering data into it
via the portal in the Goods table ... so I'm a little confused.



Helpful Harry                   
Hopefully helping harassed humans happily handle handiwork hardships  ;o)

Helpful Harry wrote:

> In article <cqenij$cm8$1@news.hispeed.ch>, "Hans Hilper"
> <hhn10@gmx.net> wrote:
> 
> > Hi
> > 
> > I am a filemaker newbie working with version 6. I need to manage
> > goods ordered from different suppliers.
> > 
> > I have the following tables and fields:
> > - Table Goods with fields GoodID and GoodName
> > - Table Suppliers with fields SupplierID, SupplierName and more..
> > - Table GoodsSupplier with the fields SupplierID and GoodID as well
> > as GoodName and SupplierName, which are calculated fields to get the
> > values from the other two tables.
> > 
> > Now my problem: In a layout in the Goods table I have a portal
> > showing the suppliers for this good. I want to have a text field
> > with a popup, where all suppliers are displayed and the user can
> > choose one, but in the record I want to save the corresponding
> > SupplierID. Right now I defined a value list with the two fields
> > SupplierID and SupplierName to choose from. I don't want to display
> > the ID, but only the name...
> > 
> > regards
> > Hans
> 
> If I'm reading this correctly, all you need is a relationship link
> from from the GoodsSuppliers table to the Suppliers table using the
> SupplierName as the linking field,
> ie. 
>       SuppliersLink (in GoodsSuppliers)
>       SupplierName = Suppliers::SupplierName
> 
> then the field SupplierID in the GoodsSuppliers table can be a
> Calculation field retrieving th data via this new relationship,
> ie.
>       SupplierID {Calculation, Text Result, Unstored}
>           = SuppliersLink::SupplierID
> 
> 
> BUT, you say above that SupplierName in the GoodsSuppliers table is a
> Calculated field, which it can't be since you're entering data into it
> via the portal in the Goods table ... so I'm a little confused.
> 
> 
> 
> Helpful Harry                   
> Hopefully helping harassed humans happily handle handiwork hardships
> ;o)

OK, let's try again ;-)

Maybe I can pout my question simpler. I am in a layout working on
goods. I have a portal showing all the suppliers for this good, this
records are saved in a second table, GoodsSupplier, where I want to
store the ID from the good as well as the id from the SUpplier.

Now in my portal I want the user to choose from a list of
SupplierNames, not their respective SupplierIDs.

I created a valuelist of all supplier, consisting of their ID and NAME.
I need the ID to save in the record and the NAME to display. Right now
I have both the ID and the name displayed, but I only want the name
displayed and the id saved. How can I do this?

Hopefully my question is now better understandable ;-)

regards
Hans

In article <cr0vl6$9f8$1@news.hispeed.ch>, "Hans Hilper"
<hhn10@gmx.net> wrote:
> 
> OK, let's try again ;-)
> 
> Maybe I can pout my question simpler. I am in a layout working on
> goods. I have a portal showing all the suppliers for this good, this
> records are saved in a second table, GoodsSupplier, where I want to
> store the ID from the good as well as the id from the SUpplier.
> 
> Now in my portal I want the user to choose from a list of
> SupplierNames, not their respective SupplierIDs.
> 
> I created a valuelist of all supplier, consisting of their ID and NAME.
> I need the ID to save in the record and the NAME to display. Right now
> I have both the ID and the name displayed, but I only want the name
> displayed and the id saved. How can I do this?

You need a relationship link in the file / table that is going to be
saving the Name and ID. This new relationship links back to the
Supplier file / table using their Name, not their ID. 
ie.
      Link GoodsSupplier to Supplier
      when SupplierName = Supplier::SupplierName

Then your saved ID in that file / table can simply be an auto-enter or
Calculation field using this new relationship - probably an auto-enter
since it's likely being used for other relationship links:
eg.
      SupplierID {Text, auto-enter calculation}
         = If (IsEmpty(SupplierName), "", Supplier::SupplierID)

You could also use the auto-enter Lookup option vis this new
relationship link to achieve the same result.

The only problem using auto-enter is that you can't go back and edit
the related data later - if you change the name of the supplier then
the ID won't automatically change. You're usually best to delete the
related record and make a new one.



Helpful Harry                   
Hopefully helping harassed humans happily handle handiwork hardships  ;o)

Helpful Harry wrote:

> In article <cr0vl6$9f8$1@news.hispeed.ch>, "Hans Hilper"
> <hhn10@gmx.net> wrote:
> > 
> > OK, let's try again ;-)
> > 
> > Maybe I can pout my question simpler. I am in a layout working on
> > goods. I have a portal showing all the suppliers for this good, this
> > records are saved in a second table, GoodsSupplier, where I want to
> > store the ID from the good as well as the id from the SUpplier.
> > 
> > Now in my portal I want the user to choose from a list of
> > SupplierNames, not their respective SupplierIDs.
> > 
> > I created a valuelist of all supplier, consisting of their ID and
> > NAME.  I need the ID to save in the record and the NAME to display.
> > Right now I have both the ID and the name displayed, but I only
> > want the name displayed and the id saved. How can I do this?
> 
> You need a relationship link in the file / table that is going to be
> saving the Name and ID. This new relationship links back to the
> Supplier file / table using their Name, not their ID. 
> ie.
>       Link GoodsSupplier to Supplier
>       when SupplierName = Supplier::SupplierName
> 
> Then your saved ID in that file / table can simply be an auto-enter or
> Calculation field using this new relationship - probably an auto-enter
> since it's likely being used for other relationship links:
> eg.
>       SupplierID {Text, auto-enter calculation}
>          = If (IsEmpty(SupplierName), "", Supplier::SupplierID)
> 
> You could also use the auto-enter Lookup option vis this new
> relationship link to achieve the same result.
> 
> The only problem using auto-enter is that you can't go back and edit
> the related data later - if you change the name of the supplier then
> the ID won't automatically change. You're usually best to delete the
> related record and make a new one.
> 
> 
> 
> Helpful Harry                   
> Hopefully helping harassed humans happily handle handiwork hardships
> ;o)

Helpful Harry, thank you very much for your help!! I send our customer
the database this morning and hope to never ever hear from him again :-)

best regards
Hans