I'm trying to find records where users have tried to include two email addresses into a field only designed for one, The manual says that INDEX_OF can take an optional third parameter to denote the occurrence of a string but I keep getting an error saying can't be converted in my version of SQL (MySQL 5.7)
The search is FIND Members WHERE INDEX_OF('@',Members.EmailAddress,2)>0
So - in other words, if there is more than one @ in the email address string then bomb them out. Is there another way of achieving this? OR alternatively is there a syntax that will allow AIM to send to more than one address - something like [email protected];[email protected]
Am asking here because I don't run a mail server on my Dev server and my production server's mail server is locked down to prevent relaying and I don't want to play around in a live database
Problem with INDEX_OF
Problem with INDEX_OF
Rocketman
V8.7 Developer Edition. Server 2016 Standard edition. MySql 5.5
V8.7 Developer Edition. Server 2016 Standard edition. MySql 5.5
Re: Problem with INDEX_OF
Rocketman,
I do not think you can use INDEX_OF as part of a query.
There might be better ways to do what you want, but here is one way:
Use the EXE_SQL function like
If you are using the default AwareIM database, you will need to add your BSV name, followed by the under score (_), with the first Members like:
This will put Members with multiple email addresses into Context just like a FIND statement.
I do not think you can use INDEX_OF as part of a query.
There might be better ways to do what you want, but here is one way:
Use the EXE_SQL function like
Code: Select all
EXEC_SQL `SELECT * FROM Members WHERE EmailAddress LIKE '%@%@%'` RETURN Members
Code: Select all
EXEC_SQL `SELECT * FROM BSV_Members WHERE EmailAddress LIKE '%@%@%'` RETURN Members
Bob
-
- Posts: 1457
- Joined: Tue Jan 24, 2017 5:51 am
- Location: 'Stralya
Re: Problem with INDEX_OF
If you have users adding multiple email addresses then they must be using some delimiter to separate them, like SPACE, ";" , "," or some other character that is invalid for an email address.rocketman wrote: ↑Tue Feb 08, 2022 2:57 pm
The search is FIND Members WHERE INDEX_OF('@',Members.EmailAddress,2)>0
So - in other words, if there is more than one @ in the email address string then bomb them out. Is there another way of achieving this? OR alternatively is there a syntax that will allow AIM to send to more than one address - something like [email protected];[email protected]
Would it not be easier to look for these symbols in your query?
Presumably this is a recovery exercise to fix rather than an ongoing measure, as you'd be better to manage this via business rules or field validation at BO creation
Re: Problem with INDEX_OF
Correct - and in fact I've done just that and found lots of errors including a .co. and some with carriage returns at the end. The actual attribute is meant to be formatted as e-mail (It's the standard AIM EmailAddress attribute) so I'm failing to understand why its not rejecting these as a matter of course.Presumably this is a recovery exercise to fix rather than an ongoing measure, as you'd be better to manage this via business rules or field validation at BO creation
Rocketman
V8.7 Developer Edition. Server 2016 Standard edition. MySql 5.5
V8.7 Developer Edition. Server 2016 Standard edition. MySql 5.5
-
- Posts: 1457
- Joined: Tue Jan 24, 2017 5:51 am
- Location: 'Stralya
Re: Problem with INDEX_OF
Got it - if this is a once off (and you don't have 10 million records, this might be better done via an export or if there are millions of records via an ETL tool like Pentaho Data Integration. There is a paid and a community version available.
Personally, I'd export it into a spreadsheet identify the errored rows then create another BO off the results from that
Eg BOErroredEmails with attributes back to the BO that has the errors.
Personally, I'd export it into a spreadsheet identify the errored rows then create another BO off the results from that
Eg BOErroredEmails with attributes back to the BO that has the errors.
-
- Posts: 1457
- Joined: Tue Jan 24, 2017 5:51 am
- Location: 'Stralya
Re: Problem with INDEX_OF
Another alternative is to run a process using the INDEX_OF against every instance and then set a flag on the BO with either a count of the number of @ or just ExcessEmails= Y/N
Then run the query on ExcessEmails = Y or EmailCount>1
Then run the query on ExcessEmails = Y or EmailCount>1