Showing Duplicate Records

[Jamesferraby]

Hi, pulling my hair out here!

I would like FileMaker to build a setlist based on which staff member knows what song.

I have a database of songs and a database of players

I have a join table that links these - it looks like this:

STA01   SNGID03

STA01   SNGID04

STA05   SNGID03

STA05   SNGID04

STA05   SNGID07

STA07   SNGID03

I want to do take the above information and say something like this...

Find: STA01 and STA05

Constrain find to all matching songs.

*using the info above that would be...

STA01   SNGID03

STA01   SNGID04

STA05   SNGID03

STA05   SNGID04

That would give me a setlist of songs that BOTH STA01 and STA05 know. I tried to do this through a portal and failed miserably - I couldn't work out a calculation that would give me both - on reading some documentation it seems that you can only find "1 set" of criteria through a portal relationship?

So I tried using a script - "Find matching records" after the initial "Find of STA01 & STA05" but that only returns 1 "set" of matching records... not multiple records that all match...? If you get what I mean.

I also tried using the "!" to find duplicates but that does not work either.

Please could someone point me in the right direction - I am I even thinking about it the correct way!?

Thanks!

James

[Wim Decorte]

by far the easiest for you would be to use a multiline key on the left side of the relationship with the IDs of the selected staff:

So return delimited list in a global text field:

STA01

STA02

 

Relate to the STA field in the join table will give you a relationship (to use in a portal) to show the songs that they know.

Now that may still show duplicate songs in there.  You can solve that by using a SQL query for instance to ask for the 'DISTINCT' songs that match both staff but that's a little more advanced, let us know if you're up to working on that.

[Jamesferraby]

Ah - I initially did think of that and have done that in the past for a similar problem - but thought it was frowned upon to use that technique as it wasn't "done properly" like that?

I don't get why this is such a difficult problem - I feel like it should have a simple solution... hence the need for help

I would definitely need to show ones that matched - but then not show duplicates.. I was thinking of just showing 1 person's song list..? After it has matched the criteria it shouldnt matter as it would be the same for all?

Thanks for helping me out so quickly!

Using the return delimited list I now have a portal that shows the songs that each band member knows.

What I want to show is what song each band member knows - but then only the ones that they ALL know as a team..

so if 3 players know a certain song but one player doesnt... it wont show up. All 4 of them need to know the song for it be in the setlist type thing..

[Wim Decorte]

pseudo-code for an ExecuteSQL() call that would give you this:

SELECT DISTINCT(song_name) FROM join WHERE staff = 'STA01' or staff = 'STA02'

or ask for the join id primary key and set that in the global text field to trigger the relationship to the songs table (not the join table)

[Jamesferraby]

So would this been in the portal - filter records bit? I'm not sure I'm typing it right:

ExecuteSQL ( "select distinct from T16x_projects_SETS||id_staff| 2::id_song where id_staff = 'STA01' " ; "" ; "" )

[Jamesferraby]

I have been learning about ExecuteSQL () so I can understand better and I can't even seem to get this simple statement to work ( as a baseline)... 

ExecuteSQL ( "SELECT id_song FROM T26_SET_SETLISTS WHERE id_staff = STA01"; ""; "" )

Following the documentation - that should show me the songs that STA01 knows? I ask this in case there is in an option to "turn off SQL" or something. I only get a "?" in the field

 

[Fitch]

Try it like this:

ExecuteSQL ( "SELECT id_song FROM T26_SET_SETLISTS WHERE id_staff = ?"; ""; "" ; "STA01" )

 

Another way you might look at finding common songs is to get a list of the songs each person knows and then see where those lists intersect. Say you have two global fields to select the staff members, each one related to the join table:

Let([
staA = List( relatedA::songs ) ;
staB = List( relatedB::songs ) ;
result = FilterValues( staA ; staB )
]);
result )

[Jamesferraby]

This worked perfectly -

 

ExecuteSQL ( "SELECT id_song FROM T26_SET_SETLISTS WHERE id_staff = ?"; ""; "" ; "STA01" )

 

Is there a way of doing this?

 

$Guitarist = T16_Projects::id_staff Guitarist

 

$Drummer = T16_Projects::id_staff Drummer

 

$Vocals = T16_Projects::id_staff Vocals

 

$Bass = T16_Projects::id_staff Bass

 

ExecuteSQL ( "SELECT DISTINCT id_song FROM T26_SET_SETLISTS WHERE id_staff = ?"; ""; "" ; "$Guitarist" and "$Drummer " and "$Vocals" and "$Bass")

[comment]

14 hours ago, Jamesferraby said:

That would give me a setlist of songs that BOTH STA01 and STA05 know.

First thing: If you want to get a list of songs, then do the find in the Songs table. Then you won't have any duplicates in the resulting found set.

To find songs that both staff members know, do a find for one staff member (searching in the related StaffID field), then constrain the found set using the other staff member's ID as the criteria.

---
To clarify: I am referring to Filemaker's native Find mechanism, not the ExecuteSQL() function.

 

Edited May 3, 2017 by comment

[Wim Decorte]

6 hours ago, Jamesferraby said:

So would this been in the portal - filter records bit? I'm not sure I'm typing it right:

ExecuteSQL ( "select distinct from T16x_projects_SETS||id_staff| 2::id_song where id_staff = 'STA01' " ; "" ; "" )

No, not as a portal filter but I guess you could use like that.  I would personally use the ExecuteSQL() to collect the IDs, set the IDs in a global text field as a multiline and use it to build the relationship.

[Jamesferraby]

13 hours ago, comment said:

First thing: If you want to get a list of songs, then do the find in the Songs table. Then you won't have any duplicates in the resulting found set.

To find songs that both staff members know, do a find for one staff member (searching in the related StaffID field), then constrain the found set using the other staff member's ID as the criteria.

---
To clarify: I am referring to Filemaker's native Find mechanism, not the ExecuteSQL() function.

 

 

Songs are only assigned to Staff in the join table e.g.

Record 1: STA01 | SNG01

Record 2: STA01 | SNG02

Record 3: STA05 | SNG 01

Record 4: STA05 | SNG 03

Record 5: STA05 | SNG 06

If I then search for STA01 - I will get a list of songs that STA01 knows.

If I then extend the found set to STA1 and STA05 I will get a list of every song STA01 and STA05 knows... NOT that STA01 and STA05 BOTH know.

If:

STA01 knows: Mr Brightside (SNG01), All Night Long (SNG02), Nothing Else Matters (SNG03)

STA05 knows: Mr Brightside (SNG01), All Night Long (SNG02), Go Your Own Way (SNG04)

The list should return: Mr Brightside (SNG01), All Night Long (SNG02)

9 hours ago, Wim Decorte said:

No, not as a portal filter but I guess you could use like that.  I would personally use the ExecuteSQL() to collect the IDs, set the IDs in a global text field as a multiline and use it to build the relationship.

 

Good thinking.. let me fumble around for a while and get it mostly wrong until I get it right... or not

[comment]

23 minutes ago, Jamesferraby said:

If I then extend the found set to STA1 and STA05 I will get a list of every song STA01 and STA05 knows... NOT that STA01 and STA05 BOTH know.

But I did not suggest extending the found set. On the contrary, I suggested constraining it. So if you find the songs that STA01 knows, then constrain it to only songs that STA05 knows, you will end up with a list of songs that both know.

 

25 minutes ago, Jamesferraby said:

Songs are only assigned to Staff in the join table 

I am well aware of that. Still, the Find should be performed in the Songs table, because you want a list of songs, and you want it without duplicates. You can easily search related fields. Here's an example of script that finds songs that both STA01 and STA05 know:

Go to Layout [ Songs ]
Enter Find Mode [ ]
Set Field [ JoinTable::StaffID; "STA01" ] 
Perform Find [ ]
Enter Find Mode [ ]
Set Field [ JoinTable::StaffID; "STA05" ] 
Constrain Found Set [ ]

 

[Jamesferraby]

Ah 

52 minutes ago, comment said:

But I did not suggest extending the found set. On the contrary, I suggested constraining it. So if you find the songs that STA01 knows, then constrain it to only songs that STA05 knows, you will end up with a list of songs that both know.

 

I am well aware of that. Still, the Find should be performed in the Songs table, because you want a list of songs, and you want it without duplicates. You can easily search related fields. Here's an example of script that finds songs that both STA01 and STA05 know:

Go to Layout [ Songs ]
Enter Find Mode [ ]
Set Field [ JoinTable::StaffID; "STA01" ] 
Perform Find [ ]
Enter Find Mode [ ]
Set Field [ JoinTable::StaffID; "STA05" ] 
Constrain Found Set [ ]

 

2

 

Aha! Got it working this way - Lots to chew on and experience gained!

Thanks so much for the help and patience everyone!

[Fitch]

22 hours ago, Jamesferraby said:

Is there a way of doing this?

ExecuteSQL ( "SELECT DISTINCT id_song FROM T26_SET_SETLISTS WHERE id_staff = ?"; ""; "" ; "$Guitarist" and "$Drummer " and "$Vocals" and "$Bass")

You can use as many wildcards as you wish. They're evaluated in order, so you simply add the criteria in order. Using the wildcard rather than putting the criteria inline tends to save a lot of headaches. E.g.:

ExecuteSQL ( "SELECT DISTINCT id_song FROM T26_SET_SETLISTS
WHERE id_staff = ? OR id_staff = ?" ; ""; "" ;
$Guitarist ; $Drummer )

I don't think this query will give you the results you're looking for, but if you're interested in going further, start here:

https://filemakerhacks.com/2012/10/19/the-missing-fm-12-executesql-reference/

[Jamesferraby]

14 hours ago, Fitch said:

You can use as many wildcards as you wish. They're evaluated in order, so you simply add the criteria in order. Using the wildcard rather than putting the criteria inline tends to save a lot of headaches. E.g.:

ExecuteSQL ( "SELECT DISTINCT id_song FROM T26_SET_SETLISTS
WHERE id_staff = ? OR id_staff = ?" ; ""; "" ;
$Guitarist ; $Drummer )

I don't think this query will give you the results you're looking for, but if you're interested in going further, start here:

https://filemakerhacks.com/2012/10/19/the-missing-fm-12-executesql-reference/

 

Sweet - I can see this being VERY useful! Thanks!