Unindexed data to go into drop down list

[Wikisnoodle]

I'm creating a db to replace an Access db. Features such as drop down lists are to match as near as possible.

The Access db has a drop down with 4 items in it. I can join 3 of them using a calculation feld, leaving the other one free to 'drop' into the field. But one of the 3 items is in a related db. How do I get around not being able to list unindexed data?

[comment]

Can you provide a more detailed description?

[Wikisnoodle]

Thanks for taking an interest.

The 4 items in the drop down list are: Enquiry No; Enquiry Date; Client Code; Client Name.

The first 3 fields belong to the layout; Client Name is in a related db (and doesn't appear in this layout).

Enquiry No is what will be chosen to drop into the field.

The list will be sorted by Enquiry Date.

Client Code is filled by choosing from another drop down list (value list from another file).

Client Name, if it were a field in this layout, would fill automatically when the user chose a Client Code.

I can join Enquiry Date, Client Code and Client Name in a hidden calc field and specify that as second item in the drop down list (Enquiry No being the first). This would achieve what I want except that, because the 'Client Name' is coming from a related db, it's not indexable, causing the list to fail to be created.

Hope that makes more sense of it.

[comment]

You can use an unstored calculation field as the second field of a value list - as long as you don't select "Show values only from second field" or "Sort values using: Second field".

If that's not suitable, move to another method of selection, e.g. selecting from a portal or from a list view in a pop-up window.

[Wikisnoodle]

Thereby lies the problem, it's sorted by the date (first item in the second field). I was hoping for a solution that worked the way the Access db works (that's what the client is expecting).

Thanks for the alternative suggestions, I may have to give them a go.

[Wikisnoodle]

Probably no-one else will need this, but I slept on it and came up with a solution, so here it is...

4 items in a drop down menu with one of them originating in a related db (ie: unindexed)

The 4 items in the drop down list are: Enquiry No; Enquiry Date; Client Code; Contacts db::Client Name.

Enquiry No to 'drop' into the field.

The list to be sorted by Enquiry Date.

1 New hidden calc field in Contacts db called Client Code+Name (made from 2 existing fields).

2 New (unused) drop down, listing contents of Client Code+Name.

Three fields in Enquiries db...

3 Client Code+Name filled by choosing from a drop down list (value list from Contacts db).

4 cClient Code, identical* to above field and sitting directly over it with no browser or find access (a bodge but it works!),

calc to be <Leftwords (Client Code+Name; 1)>.

5 hcEnqDate+ClientCode+Name is a hidden field, calc to be <" – " & Enquiry Date & " – " & Client Code·Name>.

6 Drop down list on Enquiry No to be 1st = Enquiry No; 2nd = hcEnqDate+ClientCode+Name, sorted by 2nd.

Couldn’t have Access capable of something FM couldn't do!

[comment]

I am not sure I understand your description. If you want ClientName as a stored field in the Enquiries table, you can make it a lookup. However, if the client name is modified in the Clients table, the stored lookup field will NOT be modified automatically.