Re: [libreoffice-users] Re: Base scenario

Hi Ian,
First things first. I'm assuming that if you were to manually insert the
"Member ID" in the appropriate text box on the subform, then the emails
you're searching for will appear. That is the impression I received from
reading your last post on this topic. Is this the case? If so, the list box
should be able to solve the problem. If your form doesn't behave that way,
I don't understand your problem.

As for concatenating First and Last Names, I have been working with the
embedded HSQL in LO and my MYSQL is a bit rusty. However I think using the
UPDATE key word is incorrect as you've applied it. You are , I think,
trying to add a new Column, ie. "FullName" to your member table, and
populate it with the full names of your members. . UPDATE  is used to
change  values. The keywords to add columns to a table is ALTER TABLE
tablename ADD [COLUMN] columname datatype. Check my syntax it's rusty! Then
you'll have to insert the full names into the new column in the altered
table. Why not create a view instead, using the CONCAT function.

Concatenating last name and first name in MYSQL and creating a view with
the concat function:
CREATE VIEW viewname
SELECT
Member ID,
CONCAT (LastName, '  ',FirstName) AS FullName
FROM TableName;

to check if this works execute this select statement:
SELECT*
FROM viewname

Your list box should work from this view if properly set up.
Hank.




On Tue, Jan 1, 2013 at 9:53 AM, Ian Whitfield <whitfield@telkomsa.net>wrote:

>  *Hi Hank*
>
> Thanks for your reply to me - appreciated!!
>
>
> *On 01/01/2013 10:21 AM, Hank Alper wrote:
> *
>
> *Hi Ian,
> I'm not sure what you mean by automatic.Would you consider a list box with
> a drop down list of all the member names with the equivalent member ID
> being the input value  to the subform automatic enough? I've used this
> approach many times on the project I'm working on.
> In your form edit mode in the design view, click on the list box icon and
> place a list box on your subform and follow the directions. Your source
> table for the list box display will be your Member table and
> the data to appear in the list box will be your "member name"  attribute
> of that table. Finally, you will select the matching attributes from the
> two tables which links them. The Member ID number In the e-mail table and
> the Member ID from your member table (I'm guessing) will be the attributes
> you'll link. In this way, when you click on a name in the list box, you'll
> be inserting " member ID" into that form.
> Hank  *
>
>
> This sounds interesting and I can live with this answer although I must
> say I find it strange that My SQL can not automatically pick up a value
> like this!!
>
> Something like - MembersID = DatabaseName.TableName.FieldName
>
> or maybe
>
> MembersID = FieldName IN Parent Form
>
> Anyway - as I said I can use your idea. SO - I have set-up the List Box as
> you suggested and it brings-up all the names but does NOT put in the
> MembersID but rather just the Name??
>
> Secondly - at the moment I have First Names and Surnames in separate
> fields so I decided to create a new field called "FullName" and now I'm
> trying to automate this. Googling for info on this I found the perfect
> answer to what I'm doing here .....
>
>
> http://stackoverflow.com/questions/5774532/mysql-combine-two-columns-and-add-into-new-column
>
> But all it gives me is a Syntax error. I have looked and looked and found
> lots of other answers but they ALL give me syntax errors.
>
> I'm using MySQL 5.1.55 with Base as the front-end and putting in
>
> update table set FullName = concat(PrefFirstName, ' ', LastName);
>
>
> If tried everything I can think of, CONCAT_WS and looked at GROUP_CONCAT
> but no luck!!
>
> Can you see my error??
>
> Last question - Will I have to add a "Find" button to my subForm to go and
> get all the respective eMails for each member or will a filter do the trick?
>
> Thanks for your help - appreciated.
>
> --

-- 
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted