Jump to content

Counting unique portal records based on a field value


Recommended Posts

Similar to a previous post but different.

My portal contains records where a musician (MusicianID) may have multiple entries based on participation on a song (SongID). My goal is for the song count (circled) to show how many songs this musician has 'touched' regardless of the role or involvement. In order words, in this example, the song "For Good" appears twice because the musician has two records for this song in the portal - one for Cover Artist and one for Comp/Lyricist. However, the song "For Good" should only get counted once in the summary field.

The song count field is a summary field =Count of SongID in the related table.

I'm sure this is a fairly simple exercise, but I'm going in circles.

image_2021-02-17_062806.png

Link to post
Share on other sites

Assuming you have a table of Songs where each song has a unique entry, and your relationships are:

Musicians -< Roles >- Songs

a calculation field in the Musicians table defined as =

Count ( Songs::SongID )

will tell you how many songs a musician has "touched".

Alternatively, define  a summary field in the Songs table to count the SongID field (or any field that cannot be empty) and place it on the layout of Musicians.

 

Edited by comment
Link to post
Share on other sites

Thanks for replying, Comment.

SongID doesn't exist in the Musicians table - only in the Roles table (and obviously Songbook table). So I can't count SongID in the Musicians table - only in the Roles table where the MusicianID appears one or more times.

I'm not sure I'm articulating the challenge properly, but happy to keep trying. I appreciate any help I can get.

image_2021-02-17_064726.png

Link to post
Share on other sites
13 minutes ago, Clayton King said:

SongID doesn't exist in the Musicians table

I should hope not. What I suggested is to count the related field in the Songs table. Going by your screenshot, it should be =

Count ( Songbook::ID )

 

 

Edited by comment
Link to post
Share on other sites
29 minutes ago, comment said:

What I suggested is to count the related field in the Songs table. 

 

Now I'm feeling dense... I had the original count field in a one-row portal from the Roles table. You've just proved that sometimes things are much easier than we want to make them.

Thanks so very much for your patience - Some difficult concepts I grasp quite easily, and some simple concepts elude me! 

Link to post
Share on other sites
On 2/17/2021 at 7:00 AM, comment said:

What I suggested is to count the related field in the Songs table. Going by your screenshot, it should be =


Count ( Songbook::ID )

 

 

Comment, thanks for your help - it worked perfectly. Now I have a next level objective...

Here is my current relationship graph and my previous Gig layout. When this was originally created, there were just two of us performing, so I had a Singer value list created which included Clayton, Vicky and duet (both of us). The "Set List Summary & Counts" section of the layout (circled) was done with summary counts and the headings (Set, Songs, Duration, Clayton, Vicky, Duet) are just text on the layout. Each row in the Set List Summary & Counts section is a one-row portal to the same layout (Gigs~Songs_Join) filtered for the Set number. The assumption is that a max of 4 sets would be performed, thus four rows. The bottom row in the section shows how many sets, songs, duration, and counts of songs by singer.

Essentially I have several related tables that grab info into a join (Gigs~Songs_Join) table. Tables include:

 Gigs: Date/time, venue, producer, etc. I

Songbook: song title, duration, keys, genre, lyrics, etc.

Musicians: used in Songbook to include original artist, lyricist, composer, etc.

Venues: address, contact for venue, etc., used in Gigs above

WhoSang: List of singers

The attached snapshot is of the existing layout. 

We now have additional singers, so I'd like the Set List Summary & Counts section to be more dynamic to account for multiple people. Recognizing a practical limit of how big this section can be, and assuming there never to be more than four singers, I'd like to heading in the section to be dynamically created and provide the same info as currently displayed. Rather than use a Singer value list as before, the WhoSang table includes the various combinations of people.

Before, the Gigs~Songs_JOIN table had summary count fields for each of the three singers. Those are now gone since the singer is not actually a field in the JOIN table, but rather a field that looks up the SingerID in the WhoSang table. Ideally, I want is some kind of dynamic field(s) to populate the header row. In the case of duets (i.e., Clayton & Vicky), rather than show that as a duet for the purpose of the summary section, I'd like to increment Clayton and Vicky's counts. So if Clayton sings 3 songs, Vicky sings 4 songs, and they sing 3 duets together, Clayton's count would be 6 and Vicky's count would be 7. Also, if Lanny and Ella perform in a gig, they would (dynamically) appear in the section, with solo and duet counts handled the same way.

I hope this is clear enough to provide some background, and appreciate any help given! I'm stuck at  how to approach it.image_2021-02-19_185803.png.15fc5a1cc16bb12280d39cf2e58d3b4f.pngimage_2021-02-19_185704.png.4fc664368ca2a686fbe946b4701e2523.png

 

 

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Similar Content

    • By Clayton King
      First, I appreciate any help I can get as this project has been evolving over two years and, as these kinds of projects to, has become more involved and complicated over that time. I've posted related questions, but exploring other parts of my project. As I'm not a programmer, but a sort of user nerd, my inquisitive mind wants to learn, but sometimes simple concepts elude me. Feedback, both on the specific goal at hand as well as the project overall is always appreciated.
      Performers is one piece of a much larger project, but I'm trying to enhance the project and working on a piece at a time. 
      BACKGROUND
      I'm sharing the entire project layout to provide some insight into how the parts fit together. The application is a booking management tool for performers, consisting is several tables as shown:

      Again my focus right now is the Performers table and how to use it in relation to the Gigs (and Gigs~Songs_Join) tables.
      There is a Gigs layout which is the hub for performances by one or more Performers. On that layout is summary information about a gig and a portal which shows which songs were performed and in what order (Set & Slot fields). The portal also includes a Performer Name. The assumption at this point is that up to 4 performers will do a given gig. This might change in the future, and could impact the below goals.
      Gigs~Songs_JOIN is a connecting table for Gigs, Performers, Songbook, Venues, Musicians, etc. Performers is a table of singers and includes fields for a serialized SingerID, name, area of speciality (Tenor, Baritone, etc.), etc., and additional fields for combinations of the same performers including: 
      Multi: checkbox-indicates this record is more than one performer
      PerformerName: calculated based on Multi: if NOT Multi, show performers Name, if Multi, show MultiName (below)
      Type: value list-is this a duet, trio, quarter, all, etc.
      MultiName: the description of this record (i.e., Duet-Clayton/Vicky)
      Other summary fields include:
      Total Gigs: count of gigs in which this person has performed
      Total Songs: count of songs this person has performed
      GigID: match field to Gigs~Songs_Join

      GOAL
      I have two goals, one related to the other. First, if the Performer record is checked Multi, I want to "attach" or "associate" individual singer(s) to the record (i.e., show what people are "included" in the MultiName). For example, MultiName=Duet-Clayton/Vicky, I want to show that Clayton and Vicky (each in separate records) are part of Duet-Clayton/Vicky.
      Second, ultimately on the Gigs layout, I want to show which Performer sang which song (currently works in the portal), but more importantly, show variable summary information (goal layout below) which changes for each Gig as to each Performer, how many songs they sang and the total amount of time they sang. The catch is, in the summary I don't want to show the Multi records, but instead increment individual singers. So, in the summary info with related portal records in the example below, rather than showing Clayton sang one song, Vicky sang one song and Duet:Clayton/Vicky sang one song, I want to show Clayton sang 2 songs and Vicky sang 2 songs. In summary info, Duet:Clayton/Vicky wouldn't appear. In other words, the summary would show how much time each person was singing.

       In the summary block of the Gig's layout, each of the above sets is a one-row portal filtered by set. I want to have the SINGER1,2,3,4 replaced with the actual Performer's name, and each band/portal show summary info for the appropriate singer. 
      Here is my relationship graph:

       
    • By ggt667
      For FileMaker hosted solution does anyone use $$VARIABLE for external data source? I'm able to make $$VARIABLE="file:filename" work, however $$VARIABLE="fmnet:/host/filename" does not.
      In my case this would be $$FM16S00CONTACT="fm16s00.domain.tld/contact" and $$FM18S00CONTACT="fm18s00.domain.tld/contact"
      This now works, however it's unclear to me what triggered this working. I have a startup script that sets the variables. I have configure the External Data Source with $$FM16S00CONTACT, and this works, however adding another data source does not work for now, I tried quitting FileMaker and relaunch.
    • By ggt667
      Is there a way to query CWP using FMPXMLRESULT as payload to create new records in FileMaker? Goal is to be able to insert records from command line using curl from FMPXMLRESULT files to avoid a somewhat clunky query from FileMaker XML import.
      I tried the following:
      cd /tmp/ curl -X POST -kL -o /tmp/findany.fmpxmresult.xml "user:pass@127.0.0.1/fmi/xml/FMPXMLRESULT.xml?-db=PushTest&-lay=Table&-findany" curl -X POST -kL --data @findany.fmpxmlresult.xml "user:pass@127.0.0.1/fmi/xml/FMPXMLRESULT.xml?-db=PushTest&-lay=Table&-new" A new record is created, however the payload is ignored.
      Payload below FTR
      <?xml version="1.0" encoding="utf-8" standalone="no"?><!DOCTYPE FMPXMLRESULT PUBLIC "-//FMI//DTD FMPXMLRESULT//EN" "http://127.0.0.1/fmi/xml/FMPXMLRESULT.dtd"> <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult"> <ERRORCODE>0</ERRORCODE> <PRODUCT BUILD="03/16/20" NAME="FileMaker Web Publishing Engine" VERSION="18.0.4.428"></PRODUCT> <DATABASE DATEFORMAT="MM/dd/yyyy" LAYOUT="Table" NAME="PushTest" RECORDS="17" TIMEFORMAT="HH:mm:ss"></DATABASE> <METADATA> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="name" TYPE="TEXT"></FIELD> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="number" TYPE="NUMBER"></FIELD> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="date" TYPE="DATE"></FIELD> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="timestamp" TYPE="TIMESTAMP"></FIELD> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="time" TYPE="TIME"></FIELD> <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="_global" TYPE="TEXT"></FIELD> </METADATA> <RESULTSET FOUND="1"> <ROW MODID="13" RECORDID="24"> <COL> <DATA>Sweet</DATA> </COL> <COL> <DATA>667</DATA> </COL> <COL> <DATA>06/05/2020</DATA> </COL> <COL> <DATA></DATA> </COL> <COL> <DATA>10:44:15</DATA> </COL> <COL> <DATA></DATA> </COL> </ROW> </RESULTSET> </FMPXMLRESULT> If I simply put the parameters after the "?" in the payload file it works, however INSERTing the actual FMPXMLRESULT would be desirable.
      curl -X POST -kL --data @/tmp/GETlike.txt "user:pass@127.0.0.1/fmi/xml/FMPXMLRESULT.xml" Where /tmp/Getlike.txt contains
      -dbnames as pr example returns expected list of database names.
    • By Msaeed
      Hi All,
      I would like to find solve my issue, as mentioned on the topic of how can find more than fields like serial No. or Code by IOS camera. please find below the script.
       
       
       
      thank in advance for any help or support  
    • By tomp
      I'm having a problem getting BE_SMTPsend to successfully send an email with an attachment.
      No problem sending without an attachment, but when I add an attachment, I get an error 2 (not sure exactly what that means, but without the attachment, no error)
      These are the argument strings with and without attachments:

      I have tried both relative and direct file paths for the variable $attachment. Examples are:


      Both of them return an error code of 2.
      Since the 'send' without an attachment works, all the values of the fields/variables other than $attachment must be valid.
      Can anyone offer a suggestion as to what might be going on and what I might try to resolve this?
       
  • Who Viewed the Topic

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.