Counting two of the same values

[Rich]

Greets, all:

I _thought_ this would be simple: signal users that the value they're inputting into a field--in a portal--is a dupe. (For the incredibly lazy who won't scroll through the portal records, first, to see if the value's already there.) I figure a counting function ought to do it--with a count of greater than one, that would set a message window fired by a script trigger.

I know I'm close but for some reason I just can't nail it:

The calc is in the child table, JOB_KEYWORD_JOB ; the value field to test is Keyword__lxt; the foreign key is _kflt__JobID. I figure I'd count those values with the same foreign keys and keyword and that would give me the answer I need, no?

ExecuteSQL ( "SELECT COUNT ( Keyword__lxt )
FROM JOB_KEYWORD_JOB JKJ
WHERE Keyword__lxt = ? AND \"_kflt__JobID\" = ? "
; "" ; "" ; Keyword__lxt )

...but that doesn't work; I'm sure a minor tweak will fix it but I've run out of tweaks. : S  Of course, I tend to make things more difficult than necessary so if there's an easier way to accomplish the same goal, I'd appreciate it!

Ciao,

Rich

 

[Fitch]

You have two "?" but only one parameter. Not sure what the "JKJ" is, don't think it's needed. Also this might fail or be horribly slow if you don't commit the record first, but you'll need the keyword text before you commit or you'll lose track of what row in the portal you were on. So you'll need to be careful and experiment with the script trigger, possibly passing the text as a script parameter.

ExecuteSQL ( "
SELECT COUNT ( * )
FROM JOB_KEYWORD_JOB
WHERE Keyword__lxt = ?
AND \"_kflt__JobID\" = ? "
; "" ; "" ; JOB_KEYWORD_JOB::Keyword__lxt ; JOB_KEYWORD_JOB::_kflt__JobID ) 

An alternative would be to create a unique identifier in the child table on which you can then use field validation as described here:

https://community.filemaker.com/thread/96710

or you might consider using a pick list or some such method to prevent the duplicate data in the first place:

https://community.filemaker.com/thread/182738

 

[Rich]

Beauty! Thanks!! I have the FileMaker SQL guides but I'll have to find additional references to help with my SQL education; I never would've gotten the syntax right (now) not knowing about the missing parameter.

Because JOB_KEYWORD_JOB is so long, I thought I'd use an alias of it, JKJ, once the calc was nailed down.

[Fitch]

1. Missing parameters - you just have to remember that the number of question marks in the query have to be the same as the number of parameters at the end, and they evaluate in order.

2. I'd use an alias too, but not when there's just one table involved, because you only declare the table once (in the FROM clause) and if you do declare an alias, then you have to preface every field from that table with the alias ( JKJ.Keyword__lxt etc ).

[Rich]

Just a quick follow-up: From the parent table, can that calc be tweaked so it will detect if there's more than one of the same value in the child table? ...or a native FileMaker Pro calc which will detect of any of the children's records aren't unique per their relationship with the parent record? It could be a litmus test of "Are all values unique? True/False."

[comment]

See if this helps:
https://fmforums.com/topic/67941-having-two-fields-with-no-duplicated-data/?tab=comments#comment-322406