14 Replies Latest reply on Jul 9, 2015 6:25 AM by ChristopherRose

    Relationships / Tables mess.

    ChristopherRose

      Title

      Relationships / Tables mess.

      Post

      I'm new to this - using FM Pro 12 (that's all they gave me, folks).

      I'm setting up a workshop registration database.  It has four tables: Workshops, Participants, Registrations, and Commerce Items.  Each participant can be registered for multiple workshops, each workshop has multiple participants, and each workshop has various commerce items - basically the registration fees.

      The problem is that my relationships chart is starting to resemble the Pan Am route map circa 1972 and various and sundry functions aren't working properly, and when I fix one, another one breaks.

             
      • Participants has _pkParticipantID, which is an _fk in Registrations
      •      
      • Workshops has _pkWorkshopID, which is an _fk in Commerce and Registrations

      The glue that holds everything together is the Registrations table - here I have it set to assign participants to workshops, based on the commerce available for any given workshop. At first glance, it looks great - when I register a participant for a workshop, they appear in a nice ordered list on the workshop record, and the workshop appears on the participant record.  

      However, things that I've discovered I can't do:

      1) I want to include invoice data on the Registration record.

      When I select the commerce item - which successfully populates a pull down menu, filtering for only the commerce items that are assigned to the selected workshop - I want the Cost field from the Commerce table to populate the Amount Due field on the registration table, and I cannot seem to make this happen.  I've tried a script to copy the data on selection -- nada.  It always selects the first commerce item associated with the workshop, but I can't seem to get it to do a match on the _fkCommerceID on the Registrations table to the _pkCommerce ID on the Commerce Items table and copy the value from that record.

      The two tables don't have a direct relationship - they go through the workshop table.  I tried creating a direct relationship with a second occurrence of the Commerce Items table but it didn't seem to help.  I am stumped here.

      2) One of the things I'm trying to do as an "at a glance" screen on the workshop field is, for example, this.

      I have a "Dietary Restrictions" field that counts the number of registrants who are Vegan, Vegetarian, Gluten-free, etc., so that the person who handles catering will have this info quickly. It's a series of check boxes on the Participant table.

      I have two fields set up for this in the Workshop table, one, cDietaryVegan is a calculation = FilterValues ( Participant::Dietary Restrictons ; "Vegan" ), and the second - which appears on the record, is cVeganCount, which is a Count of cDietaryVegan. However, it returns a count of all participants who have the box checked (it's not restricting only to participants in a given workshop, even though it's on the workshop record page (not in a portal)). Again, I'm wracking my brain trying to figure out how to get this to work properly.

      I had a midnight brain flash that I should probably have a _pkTransactionID, but it would require new Table Occurrences, at which point the relationship graph starts to look like spaghetti and I am left with the nagging suspicion that I am vastly overcomplicating this.

      Or am I? Can anyone talk me through this? I can post screenshots, etc.

       

        • 1. Re: Relationships / Tables mess.
          philmodjunk

          It appears that you have these relationships:

          Participants-----<Registrations>----Workshops-----<CommerceItems      (----< means "one to many")

          If that's what you have you've correctly set up the classic "many to many" relationship needed between participants and workshops with Registrations functioning as the "join" table that links the two.

          Working from that interpretation of your design....

          1) It would appear that you need to pull a "cost" amount form CommerceItems into Registrations, but since there can me more than one such item related to a given workshop, you hare having trouble copying over (looking up) data from the correct table.  I would guess that you have a field in Registrations that enables you to select a specific option for a given registration in order to specify which record in CommerceItems is the correct source of this cost data.

          Adding another table occurrence of CommerceItems is one option that you can use here and is the NonSQL option that I would select to do this. (ExecuteSQL can do the same thing here but without the need for another table occurrence.)

          You would add another table occurrence (I'll name it CommerceItems|Selected) and link it to Registrations like this:

          Registrations::_fkWorkShopID = CommerceItems|Selected::_fkWorkShopID AND
          Registrations::WorkshopOption = CommerceItems|Selected::WorkShopOption

          Registrations::WorkShopOption would be the field you set up with your conditional value list for selecting which commerce option associated with the specified workshop is desired and your cost field in registrations can be set up with an auto-enter field option to copy over a cost value from CommerceItems|Selected.

          2) You might want to investigate setting up unstored calculation fields to produce these dashboard counts as a better way to go, but your calculation field should be defined as:

          ValueCount ( FilterValues ( List ( Participants::Dietary Restrictions ) ; "Vegan" ) )

          to get a count of all Vegan participants registered for a given workshop. Care needs to be taken with this type of calculation field that you specify the correct "context" table occurrence and refer to the correct table occurrence names in the expression iteself as the "match" between context and those specified table occurrence names will determine what relationships are used to evaluate the expression. Using my names above, this field would be defined in the workshops table with "workshops" specified as the context and participants would be the correct TO name (as shown) to use in the expression.

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: Relationships / Tables mess.
            ChristopherRose

            That worked perfectly!

            My next stupid question.

            Given this relationship:

            Participants-----<Registrations>----Workshops-----<CommerceItems 

            I want to list on the Workshops record the number of times each Commerce item has been selected.  (Real world speak: I want to have a summary on the workshop record where we can see how many of each registration type (aka "commerce item") has been purchased).

            Each Commerce item is specific to a workshop, but — and here's where my mental logistics breaks down - I am hoping not to have to hard code the value into the calculation because they vary from workshop to workshop.  (Also, I'm putting together this database to hand off to someone who probably won't be able to handle programming ... which, admittedly, won't be a huge step down).  

            So, to restate.

            CommerceItems are specific to a specific workshop (there are up to 5 maximum). They are identified by pk CommerceItems::__pkRegistrationSerial with _fkWorkshopID.

            I want to set a calculation on Workshops -- lets call it cRegistrationType -- for each of the 5 registration types without having to hard code into the equation the "actual value as text", but I'm stumped on how to do a find / filter / value count of a moving target.  

             

             

            • 3. Re: Relationships / Tables mess.
              philmodjunk

              This calculation would return the first commerce item for a given workshop record if defined to evaluate from the context of workshops--such as would be the case using a calculation field defied in workshops:

              GetNthRecord ( CommerceItems::ItemType ; 1 )  // use your field name for this in place of "ItemType"

              So you could define a summary field in CommerceITems as the "count of" __pkCommerceItemID (or any field in CommerceItems that is never empty.) I'll refer to that field as sItemCount.

              You can then put a one row portal to CommerceItems on your Workshops layout. Give it this portal filter:

              GetNthRecord ( CommerceItems::ItemType ; 1 ) = CommerceItems::ItemType

              Put sItemCount as the only field in that one row portal.

              Once you get the portal positioned with the appearance options that you like, (You can select transparent fill and line options to make the portal disappear and just how the summary field), you can make copies of this portal and just change the portal filter to use a "2, 3, 4, 5" in place of the 1 in my first example.

              You can define unstored calculation fields with GetNthRecord ( CommerceItems::ItemType ; 1 ) but changing that same value to create labels to put on the layout with each one row portal to identify the item being counted.

              • 4. Re: Relationships / Tables mess.
                ChristopherRose

                I can't get this to work - it's always returning a value of 1.  

                I'm wondering if it has something to do with this: the commerce items are stored in a table called CommerceItems, but selected and stored in records on the Registrations table.  I attempted to apply the same calculation based on the Registration table and it still returns a value of 1 for all records.  

                 

                 

                 

                 

                • 5. Re: Relationships / Tables mess.
                  philmodjunk

                  If you are getting a number, perhaps the field that you are referencing in the getNthRecord function is a field of type number instead of text? Such fields appear to store text correctly until you try to use them in a calculation.

                  • 6. Re: Relationships / Tables mess.
                    ChristopherRose

                    It's now returning a number (3), but that number is equal to the types of commerce items that are available to select from for a given workshop, not the number of times that commerce item has been selected (which should be 14 in my test set).

                    I'm feeling like there should be a value count calculation in here somewhere - can a GetNthRecord command go in to a calculation?

                     

                    • 7. Re: Relationships / Tables mess.
                      philmodjunk

                      GetNthRecord is to be used in the portal FILTER. A "count of" summary field defined in the portal's table is then placed inside the row of this single row portal in order to show the count of records of that type.

                      • 8. Re: Relationships / Tables mess.
                        ChristopherRose

                        I understand that.

                        However, the "count of" summary is still calculating the number of items available to select, not the number of times each item has been selected.

                        I have, on table CommerceItems, created a summary field sRegTypes that is based on "Count of __pkRegistrationSerial" (this is the auto-enter non-modifyable serial number for each unique commerce item).

                        On table Workshops, I create a portal with filter: GetNthRecord ( CommerceItems::Registration Type ; 1 ) = CommerceItems::Registration Type

                        Into this portal I place the field sRegTypes.  It returns a value of 3, which is the number of available Registration types to select from that are related to this specific workshop. It does not return the number of times that registration has been selected (14 for the 1st available record type, which is what I want to calculate).

                        • 9. Re: Relationships / Tables mess.
                          philmodjunk

                          Did you add a portal filter?

                          Is the summary field properly located inside the portal row and from the correct table occurrence? (must be from the same as the portal)

                          While in layout mode, move your portal. The summary field inside the portal row should move when you move the portal. If not, you have some how "divorced" the field from the portal. Drag it totally away from the portal, release the mouse button and drag it back. be careful to not release the mouse button until the field is fully inside the borders of the portal row.

                          • 10. Re: Relationships / Tables mess.
                            ChristopherRose

                            Unfortunately, the answer to all of these questions is yes.

                            • 11. Re: Relationships / Tables mess.
                              philmodjunk

                              Oops. I reread the previous posts and see the issue.

                              The portal and the summary field have to be from Registrations. The portal filter's GetNthRecord, however, must refer to CommerceItems.

                              So a value of "1" was correct--for counting records in CommerceItems instead of registrations.

                              • 12. Re: Relationships / Tables mess.
                                ChristopherRose

                                It's still returning a value of 3.

                                I may have to admit defeat on this one.  There's clearly some relationship in there that I haven't configured correctly in order to make this work.  Thanks for your help, though, I appreciate it.

                                 

                                • 13. Re: Relationships / Tables mess.
                                  philmodjunk

                                  Compare your file to this one: https://dl.dropboxusercontent.com/u/78737945/CommerceItemsExample.fmp12

                                  It's not been set up to be "pretty" nor to reproduce every feature that we've discussed here, but you'll find two workshop records with filtered portals to Registrations that count the number of registrations for each of two specified commerceItems.

                                  • 14. Re: Relationships / Tables mess.
                                    ChristopherRose

                                    I'll check this out later today - the parts of my job that I actually get paid for are pressing this morning :)  

                                    Thanks!