4 Replies Latest reply on Aug 21, 2014 10:09 AM by DuganMorgridge

    Portal Problems

    DuganMorgridge

      Title

      Portal Problems

      Post

           I have a database tracking contracts that my company (an agency, basically) makes on behalf of performing artists, with the people who are presenting them (and paying for their performances).

           Since some of our artists collaborate with other of our artists or sometimes with outside guest artists, I need to be able to assign more than one artist to a given contract, and divide the total fee between the participants.  Each contract only ever has a single presenter - whoever is footing the bill.  In very rare cases, there are two funding sources and I either make a separate contract for each one with separate amounts, or one agrees to be the contract party and "subs out" to the other entity.

           So, I have a table called Contracts which keeps the dates, internal sales info, and details of each contract, along with standard legal terms which are copied over from global fields so that I can modify them on a contract-by-contract basis.

           I have an Artists table which keeps our artists' personal info and any pertinent info for working with them.

           I have a table, FeesAndArtists, which is basically a child to the Contracts table.  It is related to the Contracts table using the Contract# (which is not the primary key for the contracts table, but acts like one - it is a unique identifying number for each record) and records in FeesAndArtists are created from a portal on the main Contracts entry form layout. I enter the Artist ID (from a drop-down box containing values from the Artist ID field in the 'Artists' table), and the amount that individual artist is to be paid, and Filemaker auto-enters the contract number.

           I have a relationship set up between the Artists table and the FeesAndArtists table, using the Artist ID (again a unique identifier, but not technically the primary key).

           So as I understand it, and as my relationship graph shows, 'Contracts' and 'Artists' are related in a one-to-many/many-to-one relationship, using 'FeesAndArtists' as a join table.  However, I tried to create a portal to display related records from 'FeesAndArtists', in the main Artists layout, to allow users to browse the contract history of an individual artist, and no records appear in the portal.

           I'm stumped.  As far as I can tell, it's all set up right.  What could be going wrong here?

        • 1. Re: Portal Problems
          philmodjunk

               It would seem that these are your relationships:

               Presentors----<Contracts----<FeesAndArtists>------Artists   (---< means "one to many")

               These statements strike me as "odd".

               

                    It is related to the Contracts table using the Contract# (which is not the primary key for the contracts table, but acts like one - it is a unique identifying number for each record

               

                    ...using the Artist ID (again a unique identifier, but not technically the primary key).

               These would seem to be exactly your primary keys and if they aren't, they should be. So I am not sure why you say "They aren't technically the primary key."

               But if all is as you describe, then a portal to FeesAndArtists, placed on the Artists layout should indeed show all the records in FeesAndArtists that are related to the current Artists record.

               If your portal to FeesAndArtitsts on the contracts layout is working and you can show data from Artists in the portal rows by adding fields from Artists, them my only guess at this point is that your portal to FeesAndArtists that is empty refers to an incorrect Tutorial: What are Table Occurrences? of the FeesAndArtists table--either that or you added a portal filter expression and it's causing all related records to be omitted.

               Ultimately, you may need to upload a screen shot of Manage | Database | Relationships if you still cannot figure out why your portal is empty.

          • 2. Re: Portal Problems
            DuganMorgridge

                 Well... perhaps I've misunderstood this advice, but somewhere along the way I picked up the idea that using meaningful data as a primary key is bad.  So, in the case of the contract number, that is why there is also an auto-entered serial value to serve as primary key.  A lot of the data in that table came from an old and poorly designed database which wasn't capable of assigning contract numbers automatically.  So they had to be done by a human and a separate list kept for reference.  The number is a 5 digit number with the first two digits representing which season the contract takes place in (based on the dates), and the last 3 being sequential.  I don't like that convention, but must keep it since it is now assumed that the contracts will be numbered in that way.  So I have an auto enter calculation set up to keep that going.

                 The case of the Artist ID is similar - it is a 2 or 3 character abbreviation of the artist's name.  Can't auto-generate that, and it's of course highly variable and very meaningful.  Again, this is a carry-over from old practice and the abbreviations are used all over the place in the office.  So again, I set up a serial number to be the "actual" primary key.

                 I am able to add data from Artists to the portal on the Contracts layout.  And definitely haven't set up any filters on the portal I'm trying to make.

                  

                 I'll putter around a bit more and see what I come up with - may try a screenshot of the graph.

                  

                  

            • 3. Re: Portal Problems
              philmodjunk
                   

                        using meaningful data as a primary key is bad.

                   It is not ideal, but you, nevertheless, are still using this field as a primary key and this is remains a bad idea. You should be using the auto-entered serial number fields in these relationships to link your tables. Needless to say that will require design changes to your database to implement, but they need not be visible changes to your user as they are primarily "under the hood" changes that protect your data integrity in a long term context. And this does not appear to be why your portal is blank so you'll need to check match field values in your records and the table occurrence references in your layout designs.

              • 4. Re: Portal Problems
                DuganMorgridge

                     OK, I sorted out the portal problem, at least.  The ArtistID field in the FeesAndArtists table was inadvertently set as a number field.  So that explains that.  Figured I must be missing somethign stupid, and there you have it, I was. 

                     Yes, eventually need to sort out all of the meaningful primary keys.  Your explanation is helpful and a good reminder of that.  Coming from the existing DB I had and importing data in, I set out relating things as they had been related, and have learned along the way. 

                     Have been working out a solution on the fly when the old db finally was unusable (numerous reports stopped working and nobody knows how to even begin fixing, not to mention the obvious obsolescence) but still having and urgent need for a somewhat functional solution (I've been the only one using it, but I'm in a very small office and my position uses the db far more than any other) to use while developing something that is ready for the whole team to pick up.  Before all is done, I imagine I'll end up making a clone copy, applying structural changes, and then importing data into that.

                      

                     Edit:  wrote all that, forgot to say... THANKS!