In LO Base, as in Access, relationships need to be with a unique primary key which needs to be an integer. That’s great for d/b integrity but useless to the human User. For example, if I want to assign an account number to a Client, I want the Account form to show the linked Client Name, not the client number. The d/b needs to know the number. I need to know the name.
In Access, this is achieved with a brilliant combo box feature that stores the linked number but displays the linked name. Filemaker has a similar feature (slightly different) but as far as I can find out, Base can only display the linked number. That is useless to the human user. How do I know of 9,999 Clients which number I want to link.
How do I achieve linking the number and displaying the name? The combo box seems unable to do that and is, therefore, useless for the purpose.
Thanks
Hello,
Use a list box.
You use forms and subforms. Don’t need to remember anything. You can also use search using different methods such as a table filter. Don’t see the need for a name vs the integer unless it is some unusual circumstance.
Thanks, Ratslinger. But a List Box doesn’t create a link between the Foreign Key and the Primary Key. the human eye needs to see the name. The d/b needs to see the number …
Sure it does. Tested earlier & re-tested it again now.
.
Have you looked at the list box?
And you got that from where?
Just need to connect to the primary key with the same field type.
Not saying this should be done. There are reasons not to. But it can be done.
Could I trouble you to show me your SQL behind id_t1 in Table 3?
Edit:
The first field in the Select statement is the field displayed. The Bound Field is the field stored. Number is relative to zero. So here the second field is stored. In this instance could also concatenate the first field with something else for further clarification.
Bingo! I finally made it work. Thanks so much for your help 
Dear Ratslinger,
I’m sorry to trouble you but I tried to create a new question on Ask LO but couldn’t find any way to start a new question/topic on the site. So please forgive me for sending this via you.
This is my question:
Can anyone help me with a SQL Query for this?
I have an accounting transaction table that records a master account receiving or paying money and anything from 1 to 10 contra accounts that the amount will be debited/credited to.
e.g. Master account receives $1,000
$400 is for contra account 1
$300 for contra account 2
$200 for contra account 3
$100 for contra account 4
$0 fr accounts 5 to 10
The table has foreign keys fkMaster and fkContra1 to 10 to link to the account table ID as well as MaterAmount ($1,000) and ContraAmount1 to 10
I want to show the transactions for individual accounts but each account may be linked to the fkMaster Account or any one of the fkContra accounts. I want to display all transactions linked to the individual account and none that don’t include this account. It should display the amount from the relevant “amount" field and display in the query "as “Amount” ’
I have tried all kinds of IF, AND, OR and IN but failed in all. I would be grateful if anyone can give me help here.
Many thanks
Martin
.
After signing in, upper right corner - New Topic.
.
Best to post new and clarify.
.
Would think structure to be Account table then Transaction table linked back to the Account. The transaction may have an indicator for Cr/Db, Date, Amt and possibly the buckets to the 10 contra accounts but this is a waste since some or many buckets may be empty. So best to create another table - Contra linking back to the transaction. A situation, no matter what, is that the contra total does not exceed the transaction total and probably must be equal.
.
You should place much thought into the entire process before deciding what SQL to use. The structure of the DB determines the SQL needed and if you continue to change the structure as you go along, so the SQL must change.
.
It would be extremely helpful to provide a sample & example of expected results when posting.
.
SQL cannot be produced without knowing structure, table names, field names and result desired.
Dear Ratslinger,
Thank you so much for your prompt reply and thank you for being so helpful and responsive. I really appreciate it.
I note your comments. Actually, I’m slowly forming some ideas of how to achieve it myself so i ‘ll keep going for a while.
Best regards,
Martin