AnsweredAssumed Answered

Mailing Lists - Find and SQL Quirks

Question asked by lkeyes on Jul 28, 2014
Latest reply on Jul 28, 2014 by lkeyes

Hi....

Just wanted to confirm a couple things that I believe are true...

 

1. There is no way to use Find or Quickfind to find the contents of a field which exceed a length of 40 characters.

 

I'm simply trying to ensure that my fields don't exceed a certain character count for a mailing list.

 

The only way I seem to able to do this is to create a separate field in my table (I called it addressfix) and then do a replace all using a function like:

IF(LENGTH(address1)>40; RIGHTWORDS(address1;2))

 

Is there a more refined way to accomplish this?

 

 

 

2. When deduping, I created a "dupettest" field which was the concatinated contents of the firstname, lastname, and address1 fields.

First I defined this as a calculated field.

 

When I queried this field using SQL, I ended up with a bunch of digits, in the results which I'm assuming were some internal representation of the results of the calculated field.

 

SELECT dupetest, count(dupetest) as num from labels, Group by count(dupetest) order by num desc.

 

As described, first, I got garbage. When I redefined the field as a text field, and then did a field replace with the calculation, firstname&lastname&address1

it worked, of course.

 

So, I think this means....no SQL queries on FileMaker fields which are described as calculations.

 

--- L

Outcomes