1 2 3 Previous Next 34 Replies Latest reply on Dec 29, 2016 5:34 PM by joelaw

    Lookup field problem

    joelaw

      Another problem that I cannot get to work?

       

      Layout for Church.  I have a field (type) in Membership table and I have lookup (Donation Name) in Donations table that finds the Member name.  When I do a lookup from Donations for LastFirstName I also want to copy data from the "Type" field from "Membership" into "Donations, Type" field.  I want to know if the Member is "Active" or "Inactive".

       

      I cannot find a way to do this?  I have tried many things but still not working.  The relationships are defined for "Type" in both tables.

       

      Joe

        • 1. Re: Lookup field problem
          philmodjunk

          The devil is in the details that you have not yet shared with us.

           

          Exactly how did you set that lookup? (There is more than one way to do this).

          • 2. Re: Lookup field problem
            joelaw

            Sorry for taking so long.  Had to go away a couple days for Christmas visits.

             

            My lookup is using the LFName field in the Donations layout using a DonationName field.  That field is a drop down list that is set to LFName in Membership table.  The lookup works but does not transfer any other data from the Membership record.

             

            I have set the field relationship from table Membership LFName to Donations field DonationName and the Type (inactive, active) fields from each table to connect.

             

            Thanks,

             

            Joe

            • 3. Re: Lookup field problem
              siplus

              I also want to copy data from the "Type" field from "Membership" into "Donations, Type" field.  I want to know if the Member is "Active" or "Inactive".

               

              why bring down data from a related table and store it into donations table when you can simply display it as a 1line portal or as related field(s) ?

               

              Academic question: do inactive members make donations ? Doesn't "donating" turn them into active members automagically ?

              • 4. Re: Lookup field problem
                philmodjunk

                And if there is only one related record as there should be here, you don't need the portal. You can just include the fields from the related table occurrence directly on the layout.

                • 5. Re: Lookup field problem
                  siplus

                  well tbh the member could have a activity history, like being active august 2010-sept 2013, then nov 2015 - july 2016 etc, which, when displayed in  a portal, could give some more info than just "active" or not, and once you got the 1 line portal it's easy to make it a 3-4 line portal, depending on it pointing to memberships or to members, but I think we both agree on the idea of "data on demand" as opposed to "calculated data by default", be it lookup or other flavours.

                   

                  I spend a lot of time convincing my customers that a database is much more efficient if you, as an user, do interact with it by pressing a button and having additional data pulled over (maybe using a popover) than giving you that data by default, just in case you might fancy to look at it.

                   

                  Data on demand as opposed to data just because we can.

                   

                  Usually I'm a luzr at this, because they don't want to do that extra click, even after explaining for 10 minutes how perception works, short term memory, info grouping, predictability, color perception and other annoying info that's obvious to most people on this forum. Blame 27" retina screens in particular and idiocy, generally speaking.

                  • 6. Re: Lookup field problem
                    philmodjunk

                    There can be reasons to look up (copy data)--such as to record membership data that might change in the future so that you have a record of what was current at the time the donation was made.

                     

                    If that's what you need, you'll need to select auto-enter field options on each such field in your donations table. Both the calculation and the looked up value options can be used for this.

                    • 7. Re: Lookup field problem
                      joelaw

                      The reason I wanted to copy the type fields is that I want to search and sort by that field for reports.  Maybe I am trying to do it the wrong way?

                       

                      I have tried to make a one line portal from Membership to Donations but cannot get it to work at all.  Also, what I have right now are the type fields from both tables displayed at the top right of layout and I still get nothing when I do a lookup of the Donation Name field.

                       

                      I will include a screen capture of the Donations layout.

                       

                      Joe

                       

                      Donations.png

                      • 8. Re: Lookup field problem
                        philmodjunk

                        I see no reason for the one line portal. But if you can't get it to work that suggests that either your relationship is not correct or the data in your match fields don't match.

                         

                        A screen shot of your relationships graph might be helpful.

                        • 9. Re: Lookup field problem
                          BruceRobertson
                          The reason I wanted to copy the type fields is that I want to search and sort by that field for reports.  Maybe I am trying to do it the wrong way?

                          There is no need to do that. You can search; and sort; by a related field.

                          • 10. Re: Lookup field problem
                            joelaw

                            OK Philmodjunk,  here is the relationship shot.  I am not using a portal, just the fields from each table.

                             

                            Joe

                             

                            Relationships.png

                            • 11. Re: Lookup field problem
                              philmodjunk

                              I was answering via iPhone and couldn't address a few comments raised in the time that I had available:

                               

                              well tbh the member could have a activity history,

                               

                              OK, but that's not what we have here. If you have a table of donations and a table of donors, there should be one and only one related donor record for any given donation record and thus no need for a portal. Setting it up as a one row portal does not harm really, but it makes the layout more complicated than necessary. If you did link to such a history table, you might still dispense with the portal if you sort the relationship so that the most recent related record is also the "first" related record.

                              • 12. Re: Lookup field problem
                                philmodjunk

                                You have two different table occurrences for membership and two different occurrences for donations.

                                 

                                On which Donations table occurrence did you base your layout? This is controlled by the "show records from" drop down found in layout setup for each layout in your solution.

                                 

                                That said, both relationships between members and donations are incorrect.

                                 

                                I will assume that you have based your relationship on CUMC Donations. To get your look up to work by an entered full name, you would need this relationship to an occurrence of Members:

                                 

                                CUMC Donations::Donation Name = CUMC Membership::LFName

                                 

                                That's it, no other match fields should be used. You could then add fields from CUMC Membership to your layout based on CUMC Donations and IF, IF, IF, you exactly enter the correct name, the fields from CUMC Membership will automatically update to show information from that table. You can find and sort on these fields just as though they are part of the Donations table.

                                 

                                Now the problems that this design can cause:

                                 

                                People's names are not unique, even when you have full first, middle and last names. Even with small numbers of members. People change their names--usually due to a change in marital status but there are also other reasons why people change their names. And names can have complex idiosyncratic spellings that make correct entry of names difficult. Not only can this make it hard to enter the correct name on your donations layout, it makes it easy to enter a name incorrectly in the memberships table. All but the first problem can create a situation where you need to change a name in the membership table, but if you change that name, the link to all of the donation records "breaks" as it was set up under the original name (or spelling of that name).

                                 

                                For these reasons, it is much better to link members to donations by a membership ID where __pkMembershipID is a field that is either a number field that auto-enters a serial number or a text field that auto-enters get ( UUID ). and _fkMembershipID is either a simple number field (serial number) or text field (UUID) with no auto-enter settings. (You would never match a membershipID field to a DonationID field like you show in your relationship graph as these two fields identify completely different things.)

                                 

                                The relationship would look like this:

                                 

                                Membership::__pkMembershipID = Donations::_fkMembershipID

                                 

                                With that relationship, I can describe 3 different approaches that enable you to select a donor by name, but link the records by IDs.

                                 

                                1) Set up a use values from field value list with __pkMembershipID as the first field and LFName as the second field. Specify the "show only second field" option. Set up _fkMembershipID on your Donations based layout with a pop up menu that uses this value list to get a value list where you can select a member by name, but the value list actually enters the ID.

                                 

                                2) Enable the "create" option for Donations in the above relationship. Put a portal to donations on your Membership based layout. When you enter data into the fields of the blank "add row" of this portal, the ID value will be automatically copied over for you. You can perform a find to find a given member's record and then log their donaiton in the portal.

                                 

                                3) You can turn this process "upside down" by using one relationship for finding a member by name that matches records by name while the rest of your database solution matches members to donations by ID. You use the first relationship to find and look up the ID that is then used in the rest of your solution. This approach uses an "auto-complete" enabled drop down list of member names set up on the donations name field while the _fkMembershipID field is set to auto enter the value of __pkMembershipID via this name based relationship. This method, however, requires scripted support to handle cases where there are two members with the same name or when a name is entered that does not match any membership record. (Then the script asks the user if they want to create a new membership record or not...)

                                 

                                For a working example of this last method that also includes detailed documentation on how to set it up see:

                                 

                                Adventures in FileMaking #2-enhanced value selection

                                • 13. Re: Lookup field problem
                                  joelaw

                                  I seem to be getting wound around the sprocket.....I have changed the relationships like you suggest (I hope) and no change. Included new relationship screen.  I am assuming that your fk is a secondary key?  I have set to automatic number for keys.  I tried pk_membership to sk_Donations and pk_membership to pk_donations and neither work.

                                   

                                  What I am trying to do is go to Donations, click on DonationName which does a lookup of all Membership LFNames.  I choose a name in the list and then continue to donation  type, amount, etc.  All works well until I try to do a report of Donations by donation LFName.  I need to only select "Acvtive or Inactive" members for that report.  Since I do not have a type (active, inactive) field in Donations, I cannot search for "Active" only Members to do that report.

                                   

                                  All other layouts pretty much work as planned.  The report is where I have problems searching for "active only" members.

                                   

                                  Joe

                                   

                                  Relationships.png

                                  • 14. Re: Lookup field problem
                                    philmodjunk

                                    Your relationships are still wrong. I said specifically that you NOT match records by matching membership ID to DonationID

                                     

                                    DonationID uniquely identifies each record in the donations table. MembershipID uniquely identifies each record in the membership table. You can have many records in Donations with the same MembershipID as you can have many donations from the same member.

                                     

                                    You need this relationship as I previously stated:

                                     

                                    Membership::__pkMembershipID = Donations::_fkMembershipID

                                     

                                    "pk" stands for primary key and is used to identify a field that is used to uniquely identify records in the table where it is defined. "fk" stands for foreignkey. It's value is not unique, but stores a value that matches to the pk field of the table named in the field name. The "__" before the pk just ensures that an alphabetic list of fields will always list this field first. The "_" before the fk ensures that any fkfields are listed just after the pk field. This is not required, it's just a convenient method for naming fields that I use.

                                     

                                    Say you have a membership record with a value of 3456 in the __pkMembershipID field. Any donations made by this member would be represented by records in the donations field table where their _fkMembershipID fields have a matching value of 3456. The __pkDonationID fields in these records would all store unique auto-entered values that have nothing to do with the 3456 value that identifies the member that made the donations.

                                     

                                    So you still need to change your relationships to be what I have suggested.

                                    1 2 3 Previous Next