Help: ExecuteSQL to find duplicate values from another table

[Loopscious]

Attached is a simple test database. There are 2 tables, TEST and TEST2. Each has input (text), record_number (auto entered serial). On the TEST table I have dup_check doing the following

 

 

ExecuteSQL ( 

 

"SELECT record_number

 FROM TEST2

 WHERE input = ?"

 

; "" ; "," ; TEST2::input)

 

The goal is to have dup_check populate with the record numbers of any records from TEST2 that have the same value in the input field. 

 

It's working to the extent that if it finds a duplicate it will put a ? in the dup_check field. I can't figure out how to get it to return the actual record number itself from TEST2 table. 

 

Any help would be appreciated!

 

Thank you.

 

[rivet]

don't see the attachment.   your calc seems to be referencing only table 2, I assume you want table1 to return a list of matching records from table2.   Try changing your last line to table1:   ; "" ; "," ; TEST1::input)

[Loopscious]

Changing the last line as suggested doesn't work, it then causes a ? to appear for every record. 

 

I have attached the file properly this time!

 

Thank you. 

TEST.fmp12.zip

[Loopscious]

I figured it out, but don't really understand how / why... here's the database with this working

 

 

TEST.fmp12 2.zip

[Lee Smith]

Automatic message

This topic has been moved from "Calculation Engine (Define Fields)" to "FQL or FileMaker Query Language".

[Fitch]

Which part of it do you need explained? The Lower() function? The point of that is that SQL is case-sensitive.

 

This is not an efficient way to find duplicates. Which may not matter if you don't have a ton of records, but I would expect this to get very slow once you get into a few thousand records.

 

The relationship name::name isn't needed for your ExecuteSQL calculation. But since you already have it, you could achieve the same result with no extra calc by simply placing the related ID field on the layout.

 

What is the actual goal, i.e., what will you do once you have identified the duplicates? And is this an ongoing process or a one-time task?

[Loopscious]

Fitch,

 

Thanks for your reply. 

 

I'm using FMP12 Advanced to build a timesheet system. Currently it's an internal tool for tracking a few hundred freelancers. Each freelancer's work day is a record in the timesheet table. And yes, over time (rather quickly) it will grow to a large number of records for which I'll want to check duplicates. I'm trying to prevent people from submitting their time more than once. 

 

Any tips you can share on how to do this most efficiently would be appreciated!

 

Thank you.

[Fitch]

Do the freelancers create their timesheet records directly in the database, or is the data imported or what?