Filter using fields in two linked forms

[Harry Sugar]

I’d like to ask the forum for help. I haven’t been able to figure out how to do this. I have two linked forms in my database, each with two fields. The Song Book form contains songs I play on my guitar. Its two fields are song title and artist. The Gigs form contains the gigs I play at. Its two fields are location and date. I’ve linked the two so in the Gigs form I can see the songs I’ve played at each gig, and in the Song Book form I can see all the gigs I’ve played each song at.

What I would like to do is create a filter that selects songs at a location with gig dates older than 8 months so that I don’t play a song again too soon. The filter would require the Song Book to use the fields in Gigs. But when I go to create a filter in Song Book, only its song title and artist fields are available.

Is there a way to do this, that is, create a filter that uses fields in both forms?

Thanks.
Harry

[john cesta]

I did the same thing with another android app to keep track of magic tricks I performed at different events.

Same type of application. I don’t know whether or not you cam filter on two different fields in two different forms.

I guess I wasn’t much help here.

[Sam Moffatt]

The challenge with the query is you need to know something that is an implied property of the relationship, not something explicit. To write the filters, you need something to query on.

What you might need to do is change your linking a little and use “JOIN” type instead of the normal M:M. You create a new form that has the links so from Gig [1:M] -> Song Performance and Song [1:M] -> Song Performance. Song Performance then becomes Gig (Link from Field), Song (Link from Field) and a calculation field.

The calculation field has a CONCAT in it that picks up Location from your Gig form and determines if the Gig date was less than 8 months ago and creates a binary flag. This will be used as a JOIN field. It’ll look like CONCAT([Location Name];" ";MONTHS([Gig Date]; TODAY()) < 8) and should create a field that looks like the Location name followed by a 1 or 0.

Then the last step is to create a new form for “Locations” that has a text field for the Location name again and another calculation field with CONCAT([Location Name];" ";0). You can then use this to create a Link to Form for “recently played”. Then when you look at that location, you can see recently played (or recently unplayed).

One word of caution, you need to refresh the calculations on the “Song Performances” records every so often to recalculate the boolean flag, Tap Forms only updates calculations when the fields they refer to change however in this case it’s the date that changes.

Something like that should work, I haven’t built it right now but it should enable you to figure out which songs have been played at a given location in the last 8 months. A little convoluted for sure though.

[Harry Sugar]

Sam, thanks so much for your help. Creating the Song Performance form with 1:M links to the other two forms did the trick. Now this form had the data from both forms and enable me to set it up.

Thanks again.

[Sam Moffatt]

Great to hear you figured out a path forward :)

[Author]

Posts