Concatenating fields in related form

users
#1

I have a Client form with ClientID, FirstName and LastName fields and a Project form with ClientID and FullName fields. In Filemaker I was able to type in a client id into the Project form and it would insert the first name and last name from the Client form into the FullName field of the Project form. How can I do this in Base?

Hope that is clear.
TIA, Adam.

#2

Hi Adam,

I have a Client form with ClientID, FirstName and LastName fields and
a Project form with ClientID and FullName fields. In Filemaker I was
able to type in a client id into the Project form and it would insert
the first name and last name from the Client form into the FullName
field of the Project form. How can I do this in Base?

In databases values shouldn't be duplicated. So "FirstName" and
"LastName" from one table shouldn't be "FullName" in the same or another
table. You will get this values through the primary key and a query. I
you want to show "Name" in one field you have to concatenate it like
SELECT "FirstName"||' '||"Lastname" FROM "Table"
If you will see this directly while input of values you have to set the
input of "ClientID" in the mainform and the query for the shown content
in the subform. "ClientID" will be the field connecting to the subform.
It could be set up as a listfield with the code
SELECT "FirstName"||' '||"Lastname", "ClientID" FROM "Table"
So it will show the "FullName" if the clientID is submitted from the
mainform.

But: Do you really know all ClientIDs? I would prefer a listfield
instead, which directly shows the names.

Regards

Robert

#3

Thank you for taking the time to reply Robert.

If you will see this directly while input of values you have to set the
input of "ClientID" in the mainform and the query for the shown content
in the subform. "ClientID" will be the field connecting to the subform.
It could be set up as a listfield with the code
SELECT "FirstName"||' '||"Lastname", "ClientID" FROM "Table"
So it will show the "FullName" if the clientID is submitted from the
mainform.

I can only get this to work (with a list box) if there is only one project for each client.
If I have two separate project records with the same client id I get an error.

But: Do you really know all ClientIDs? I would prefer a listfield
instead, which directly shows the names.

I have nearly 1000 clients. It would be tedious scrolling through those looking for a client, or am I missing something?
I was hoping I could recreate my old Filemaker method of linking the records.

Thanks,
Adam

#4

Hi Adam,

I have nearly 1000 clients. It would be tedious scrolling through those looking for a client, or am I missing something?
I was hoping I could recreate my old Filemaker method of linking the records.

Have you tried typing the first three letters of your client after
selecting the listbox ? There were a couple of known bugs reported
against this not behaving quite in the way expected, but they may or may
not have been resolved.

Unfortunately, Base is far from being as user friendly out of the box as
FileMaker when it comes to form controls, how you can bind them, and how
they behave.

Alex

#5

Hi Adam,

I can only get this to work (with a list box) if there is only one
project for each client. If I have two separate project records with
the same client id I get an error.

There must be something wrong in your database-construction. Please load
up an example with some dummy-data to see what is getting wrong. If you
couldn't load it up anywhere please send the example direct to me so I
could see the problem, which appears.

But: Do you really know all ClientIDs? I would prefer a listfield
instead, which directly shows the names.

I have nearly 1000 clients. It would be tedious scrolling through
those looking for a client, or am I missing something? I was hoping I
could recreate my old Filemaker method of linking the records.

Alex has already send an answer to this. It is no problem to type a
ClientID in a field, press a button and see all rows of the database
with this ClientID.

Regards

Robert

#6

There must be something wrong in your database-construction. Please load