Selecting the portal row

[cpking]

Started a new thread as progress has been made and I'm on to the next hurdle. I've received tremendous help from users here; their assistance has been both educational and helpful!

I have a layout with a portal (this is till my, now infamous, Gigs solution. The Gigs layout has a portal with gigs_PERFORMANCE which includes each song in a given Gig. gigs_Performances contains a unique field called PerformanceNo. To assign a singer to a given song in the gig, I have a Roster field in the portal record a script to assign singers to the song which uses a card with checkboxes. This is the thread we recently commented on.

The challenge is that when running the script on previously entered [portal] records, everything's just great. However, if I run the script on the most recently entered portal record, it doesn't find the record. For example, in the Gigs layout, clicking the scripted button on the 2nd portal line (Join 900=PerformanceNo 900) the correct record in the gigs_PERFORMANCES table is served up in the card. Notice I have (temporarily) added the fields populated by global variables as well as the actual record on the card for troubleshooting. The Roster field at the bottom of the card is what puts the initials in the Roster field.

After checking box(es),

However, when entering a new song in a gig (so the last record on the portal), the global variables are correct and the portal record does in fact exist. I know this because I have a second window open with a table view of the gigs_PERFORMANCES table and can see the record appear as I'm entering new lines in the portal on the Gigs layout. However, even though I see that record created, when I invoke the script, it doesn't find the record for me to assign singers. So when I click a checkbox in the card, I get the error message that "No records are present. To create a new record choose the New Record menu command."

I can't help but think this is a simple thing I'm missing somewhere, but I'm completely baffled.

Here's my script:

[mipiano]

Before I go to bed now, one more thing: Please try to commit the record after inserting or at the beginning of this script. If your second window is referencing the same table, you'll be in the same context. I think that’s the cause why you see the record in this second window - even if it is not committed yet.

Good night here from Germany. I’ll have a look to the thread tomorrow again.

[steve_ssh]

Hello @cpking ,

You have a Perform Find step at line #23.

Being able to see how you have configured that step (i.e. a screenshot of the options for that step when you inspect its detail) could offer some insight into what could be going awry.

[FMUSER01]

Clayton,

Some time ago I suggested you might consider using the approach of common invoice solutions; COMPANY, INVOICE, LINE ITEM, PRODUCT, RESOURCE.

I think you might benefit if you find and download the free FileMaker FTS 15 (FileMaker Training Series) from the Claris website to study their "Bonsai" invoice solution.

I was easily able to rework your file and data into a more common organization which rectified many or all of the challenges you've posted. No scripts to speak of were required.

A key point was to create a product table. I chose to use your existing Gigs~songs_JOIN table as the basis instead of your songbook table because it has songs with singer(s) combinations that were actually performed. More song attributes and data from the songbook table were merged to the new products table, however.

Mostly, the initial product table data drives the line item (performance) data you're looking for. The performances display in a portal on your Gig layout but a separate layout or card window of an individual line item is where one "mixes / matches" particular components of stock products after the attributes were looked-up to fill the detail form.

I recommend also gathering all resources such as songs as materials, singers and musicians as services, and "miscellaneous" into a single table. Doing so will offer consistency of named components from which to build products and line items and will afford other important functions.

When one modifies line items, resource data is referenced and not product data. Think of products and line items made of stacked components originally from resources.

I hope this helps.

[cpking]

Thanks for the reminder, @FMUSER01 . For good or for bad, the solution has "evolved" quite a bit since then. You may recall the complication arose after expanding the number of singers. Previously, a song was "owned" by a Singer, and thus was part of the Songbook record. At that time, there were two singers who also sang duets, so the Songbook table had a field which included either Singer1, Singer2 or Duet (which understood to be both Singer1 and Singer2). This was also how I was able to do summary calculations for the specific Gig with singer/set totals (i.e., Singer1 had x many song, Singer2 x many, and so on).

Opening up the singer list to more performers meant endless combinations of singers. Where previously adding a song to a gig would assume a given singer, now Singer1 and Singer5 might both sing the same song at different gigs.

To accomplish this, on the advice of several people, the relationship grid was updated and edited, resulting in the gigs_PERFORMANCES table (which is the portal in Gigs). The Songbook table no longer has the singer in it, and instead gigs_PERFORMANCES grabs related info from Songbook, Singers, Gigs, Composers, etc., to create the portal of songs performed.

All this said, I think the Company/Invoice/Line Item model would work and that's essentially what I have now. The single issue I'm grappling with is adding singers to the currently added song (portal record). This appears to be how and when records in the portal are committed. And what I can't get my head around is when adding a row to the portal (done by entering a SongID, Set and Slot) I can actually see the record being created in another table-view window of the same table used in the portal. It's when I go to assign a singer to the record (the "current" one in the portal), I can't access it through the card. If I go to the next gig and then back to the previous one, I can access the portal record through the "Choose a Singer" card. ...I'm not sure if my language is adequately describing what's happening.

If I invoke the script to choose a singer on an existing record, it works fine. If I invoke the script on the portal record I just created and try to check a singer's name, I get the error that No Records are Found. But the record exists in the table.

[steverichter]

@cpking - please post your current relationship graph. I’d like to see how you’re now relating the tables.

[cpking]

Just to add some clarification: as the solution has developed, some layouts and scripts used the TOs on the right. As I discover their use, I adjust and use the TOs on the left. Because of testing and development, I've been hesitant to just delete them.

[FMUSER01]

Seems you’ve yet to have a products table. I counted 132 products when I tackled it. The table is essential.

Just as the FM training series implements, a product is called via a value list of products from a portal and any stock product data is auto-filled into their separate fields on the row which will be part of a line item record.

Let's say you currently have one instance of song material which is "Carolina In My Mind" by James Taylor released in 1968. That song material is a resource. From that single song you have 3 offerings (products) of the song; one performed by yourself, another performed by the duet of Clayton & Vicky, and one by Vicky Saye Henderson. Because it's a standard product, one of those offerings are usually called upon for a particular gig.

In the circumstance of a new and different singer of the song, a user calls the song to the portal record but edits the line item from a dedicated line item detail layout or card window. This affords the opportunity to change the single attribute field(s) related to resources. Singers are a considered a service resource in this case as are musicians, accompanists, DJs, etc.

As an aside, a user would not be changing original artists, composers, etc., because that data is memorialized into the resource record as historical fact.

[steverichter]

@cpking — Try giving your portal an Object name and then use the following script steps:

Go to Object [ "Put object name of portal here"]

Go to Portal Row