Hi All.
I have a table in the display called Members linked to a database table called member:
Database table called member. Note – email addresses and names are fake.
I have a query which takes the selected record and, using the uid_household, puts the first_name into the salutation column (for all the records - uid_member - that share the uid_household) if the record has an email_address - as in the "Alsop" record. The query is:
The query work fine in putting the first names into the database where each member has an email_address. The query was also designed to find a blank email in any of the records associated with the uid_household and place the first_name of that member with the first_name of the member who has an email address in the salutation field - as in the "Allan" record. It doesn’t work for this second part. So I have another query which does combine the two first_name and put them in the correct salutation field but doesn't work for records that have email_address(es):
I have tried to get one query to do both jobs, as in the first query, without success. The database table for member is:
If anyone could help with this I would be most grateful.
@AlanT
The use case is the following:
If a household has one email address then combine both first names into the salutation field in the member table?
You also wrote:
"So I have another query which does combine the two first_name and put them in the correct salutation field but doesn't work for records that have email_address(es):"
But it seems that Sarah and Connor Allan have the salutation updated where only 1 of them has an email address.
Does the issue occur when both persons from the same household have an email address
Sorry, but the screenshots you posted are difficult to read...
Hi ScottR,
Thank you for the quick reply. Sarah and Connor Allan do have both names in Connor's record salutation because Sarah doesn't have an email address. Melanie Alsop (below Allan's) has her first_name only in her salutation and Lucas has his first_name in his salutation because they have an email each. The idea is when sending out an email you personalise it to the owner of the email but if another member of the household hasn't got an email address you personalise to both on the one email. I hope I have made it clearer. This screen shot may be better:
You have but now I am confused. Are you saying there are two use cases:
- Two persons from the same house hold with only ONE email address will receive an email with the Salutation containing both FIRST names of the household (account)
and
- When you have BOTH members in the SAME household and BOTH members have an email address; you cannot seem to add both names to the Salutation field that will be used in an email?
(If this is the case, it could be a simple matter of adding another WHEN clause since m1 and m2 cannot both be equal as it's only ONE selected row in the table)
You might have to say something like WHEN m1.email_address != null AND m2.email_address != null (do something here that will select both first names from those rows in the db table where this is true)
Hi ScottR, Your '1' statement is correct. Just to clarify your '2' statement - if BOTH members of the SAME household have an email_address then only their first_name will go in the salutation field in their respective record.
Hi there!
Maybe this will help? 
(needs to be refactored for your specific dataset & I'd recommend testing with a variety of cases)
WITH subquery AS (
SELECT email
FROM table_name WHERE uid_household = {{table1.selectedRow.uid_household}} )
UPDATE table_name
SET salutation = CASE WHEN EXISTS (SELECT 1 FROM subquery WHERE email IS NULL) THEN {{table1.data.filter(x=>x.uid_household ===table1.selectedRow.uid_household).map(x=>x.first_name).join(' and ')}}
ELSE first_name
END
WHERE uid_household = {{table1.selectedRow.uid_household}} AND email IS NOT NULL
Hi Tess and thank you for your help. There is a problem which is defined as -
"TypeError: Members.data.filter is not a function. (In 'Members.data.filter(x=>x.uid_household ===Members.selectedRow.uid_household)', 'Members.data.filter' is undefined)"
'Members' is my 'table1' in your solution. The query runs successfully but doesn't update the data table 'member'. I have tried some changes but with no success!
Any thoughts?
Alan
Hmm that's interesting 
*Members.data.filter is not a function *usually indicates that Members.data is not an array or there's some other issue with the data. Can we see a screenshot of the query?
Hi Tess, Thanks for the reply. Here is a screen shot of the query -
Just to clarify - 'Members' is a table in the app (your table1) and the table containing the data is 'member' (your table_name). Sorry the are very similar!
Hope this helps.
Thanks all for your ideas - appreciated. I have found an alternative way of resolving the issue. Alan