Concatenating fields in related form

classic Classic list List threaded Threaded
6 messages Options
Adam Fenn Adam Fenn
Reply | Threaded
Open this post in threaded view
|

Concatenating fields in related form

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.
--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Robert Großkopf Robert Großkopf
Reply | Threaded
Open this post in threaded view
|

Re: Concatenating fields in related form

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
--
Homepage: http://robert.familiegrosskopf.de
LibreOffice Community: http://robert.familiegrosskopf.de/map_3


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Adam Fenn Adam Fenn
Reply | Threaded
Open this post in threaded view
|

Re: Concatenating fields in related form


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
--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Alex Thurgood Alex Thurgood
Reply | Threaded
Open this post in threaded view
|

Re: Concatenating fields in related form

Le 17/09/2018 à 21:24, Adam Fenn a écrit :
>

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


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Robert Großkopf Robert Großkopf
Reply | Threaded
Open this post in threaded view
|

Re: Concatenating fields in related form

In reply to this post by Adam Fenn
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
--
Homepage: http://robert.familiegrosskopf.de
LibreOffice Community: http://robert.familiegrosskopf.de/map_3


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Adam Fenn Adam Fenn
Reply | Threaded
Open this post in threaded view
|

Re: Concatenating fields in related form


> 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.

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy