Field values from a not-yet-related record

[SlimTech]

Hi all,

 

There must be an easy, neat solution to this problem. Could anyone point me in the right direction, please?

 

Table1: Customers

Fields: Customer number

Company

etc.

 

Table2: Specifications

Fields: Customer number

Order number

etc.

 

The tables are related by Customer number. However, the option, "Allow creation of records in this table via this relationship" is not selected for either of them. I don't want this as not every Customer will ultimately lead to a Specification.

 

What I'd like is to be able to create a new Specification record at will (with "Command"+"N" on a Mac) and to be able to select the appropriate Company name from a Pop-up List. I defined a Value List consisting of all values of the field, Company from the Customers table.

 

However, I don't get a single value appearing in the Pop-up list as there's not yet a relationship between an uncommitted, new Specification record (with an empty Customer number) and any of the existing Customer records.

 

Could anyone suggest a good solution for this problem, please? I've looked for other posts addressing the same problem but couldn't find any. smiley-frown

Mike

[David Head]

Sounds like you have defined the value list incorrectly. Set it up so that it is based on a field - the customer number field with the second field being the company name. That will display all companies with no need for any relationships.

[SlimTech]

Thanks for your post, David.

 

I did indeed define the Value List as consisting of all values of the field, Company from the Customers table. Just as an experiment and to fully implement your suggestion, I removed the relationship between the Customer and the Specification tables and included Customer number in the Value list - I still don't get any Companies (or Customer numbers) appearing in the Pop-up list.

 

However, if I format my Customer number field on the Specification layout as a Pop-up list using that same value list, all Customer numbers and Companies do appear. If I choose one of them, only the Customer number stays in this numerically defined field. If there is still a relationship between the two tables (with Customer number), the Company field is assigned a value also as there's now a matching field in the Customer table. I really need the relationship for a few other data items in any case.

 

Does the difference between the two results lie in the fact that Customer number is a stored field on the Specification layout whereas Company isn't?

 

Michael Franks

[David Head]

I did indeed define the Value List as consisting of all values of the field, Company from the Customers table. Just as an experiment and to fully implement your suggestion, I removed the relationship between the Customer and the Specification tables and included Customer number in the Value list - I still don't get any Companies (or Customer numbers) appearing in the Pop-up list.

How is the field Company in the Customers table defined? I assumed it was a text field. If it is a calculation, getting a value from another table (Company), then the value list will not work because the company name cannot be indexed. Please provide all tables and relations between them.

 

However, if I format my Customer number field on the Specification layout as a Pop-up list using that same value list, all Customer numbers and Companies do appear. If I choose one of them, only the Customer number stays in this numerically defined field. If there is still a relationship between the two tables (with Customer number), the Company field is assigned a value also as there's now a matching field in the Customer table. I really need the relationship for a few other data items in any case.

This is expected - choose a customer from the value list and customer number is inserted into the field. So again the confusion - what is the Company field? text or calculation?

 

Does the difference between the two results lie in the fact that Customer number is a stored field on the Specification layout whereas Company isn't?

So you are saying that Customer number is a stored field in the Specifications table. The Company name would either be a calculated result via a relationship or simply a related field on a layout.

[SlimTech]

Giving more detail, then:-

 

CUSTOMERS table

Customer number (stored numeric, auto enter serial number)

Company (stored text - shows as being indexed in the list of fields for this table)

Business type

etc.

 

SPECIFICATIONS table

Customer number (stored numeric)

Order number (stored numeric)

Specification date

etc.

 

These two tables are related in only one manner:-

Customer number = Customer number

 

COMPANIES value list defined to consist of all values of Customers::Customer number and Customers::Company

 

SPECIFICATIONS layout

Customers::Company (formatted as Pop-up Companies value list)

Specifications::Customer number (if formatted with same pop-up, list appears!)

Specifications::Order number

Specifications::Specification date

 

So you are saying that Customer number is a stored field in the Specifications table.

Yes

The Company name would either be a calculated result via a relationship

Might be necessary, I'm not sure.

 

or simply a related field on a layout.

This is what I'm really aiming for.

 

In an earlier version of my database solution, "Company" was a primary key in Customers and a foreign key in Specifications. It occurred to me whether or not old indexing was hanging around and messing things up - I deleted the Companies values list and removed the field from the Specifications layout and re-did these things but without any improvement!

 

Mike

[David Head]

SPECIFICATIONS layout

Customers::Company (formatted as Pop-up Companies value list)

Specifications::Customer number (if formatted with same pop-up, list appears!)

Specifications::Order number

Specifications::Specification date

This is where your problem lies.

Customers::Company is a related field that will display a Company (name) based on the relationship between Specifications and Customer. It should not be an editable field. You do not want or need users changing the company name from the Specifications layout.

 

All you need is for the Specifications::Customer number field to have the pop-up list for data entry. When a customer is selected, the Customers::Company will display the selected company name.

 

TIP: Change the field behaviour of Customers::Company on the Specifications layout to not allow entry in Browse mode.

[SlimTech]

Super. I've got it all working now including in browse-mode, a non-editable display of Customers::Company. It's just a bit of a shame that I can't choose a company name directly from a pop-up list and have the Specifications::Customer number filled in!

 

Luckily (or from good Filemaker design?) a value list can be defined to display two fields since choosing from a pop-up list purely of Customer numbers wouldn't be so er user-friendly!

 

Thanks ever so much for your help, David.

 

Michael Franks

[David Head]

Super. I've got it all working now including in browse-mode, a non-editable display of Customers::Company.

Excellent. Glad it works for you now.

 

It's just a bit of a shame that I can't choose a company name directly from a pop-up list and have the Specifications::Customer number filled in!

Well there is a way to do that but you didn't ask for that. smiley-wink

 

Luckily (or from good Filemaker design?) a value list can be defined to display two fields since choosing from a pop-up list purely of Customer numbers wouldn't be so er user-friendly!

Purely by design for exactly the reason you have found.

 

Thanks ever so much for your help, David.

 

No worries Michael.