5 Replies Latest reply on Apr 30, 2016 6:58 PM by sccardais

    Using list for relationship?


      Is it possible to use a single field with multiple, return separated values to form a relationship? For example, assuming I want to track a set if unique customer id's as "cohorts." I could create a separate record to hold each customer I'd and use another field to hold a cohort I'd. Or, I could use a single record for each cohort and store all customer id's in a single field.


      Is it possible to use either technique? If so, should the list be return separated or comma delimited?


      Are there strong reasons to use one approach or the other?

        • 1. Re: Using list for relationship?

          The first approach is a join table that you will see in almost any database solution with a modicum of complexity (OrderLineItem, ActorInMovie …)


          The second one is a common technique called “multi-line key”; it works with return-delimited lists on either side of the relationship. This is most commonly used for UI purposes – e.g. find records, capture their IDs and put them in a field that drives a relationship for a results portal.


          The potential issue with this second approach is that often the association you want to express requires further attributes (from, until, membership, quantity, role name etc.) that go beyond the mere fact of the association – i.e. the association becomes an entity in its own right that needs its own attributes to describe it in full, and will get its own ID (and may even spawn child records of its own).


          So choose your method based on the (desired) complexity of your association.


          btw: If a customer can be a member in only one cohort at a time (and you don't need a history of associations), simply create a Cohort foreign key in Customers.

          • 2. Re: Using list for relationship?

            sccardais wrote:


            Is it possible to use a single field with multiple, return separated values to form a relationship?

            Yes it is.


            However, on this forum your best bet is NOT asking if filemaker can do this and that but delineate your goal, because the road you implicitly chose in order to solve your original problem might not be the best one.


            Knowing what you really want (in plain terms, like "I am on the client layout and want to see the mean value of days he took to pay his bills)" is infinitely more helpful and challenging for experienced developers: it's probably something that has been asked before, has been evaluated against big data constraints so that for example a List method has proven to be better than a SQL method, etc.


            I'm sorry to pick you for this personal rant: it's a basic problem which keeps on coming back. In a few words, it's "don't tell us what your problems are when dealing with a problem, tell us what the original problem is."

            • 3. Re: Using list for relationship?

              Fair enough. I completely understand and will be mindful in future posts ...


              In this particular situation, I’ve created a system to track utilization of certain features of our software. This system is used to generate a report showing trends over time and to help an internal team prioritize training and communications topics. Sometimes they test different methods of communicating to see which method has the most impact - as measured by an increase in utilization of certain product features.


              To do this, they create “cohorts” consisting of subsets of customers. I want to add the ability to track utilization statistics for each cohort.


              Each cohort would contain anywhere from 50 to 1,000 customers - each with it’s own unique ID. I know I could create a Cohort table with separate fields for Cohort ID and Customer ID and related this to my base table using Customer ID. The Cohort table would have one record for each customer.


              I wondered if it might be “better” to simply create one record for each Cohort with a single field to hold a list of Customer ID’s.


              I haven’t thought through all of the possible ways this Cohort relationship might be used and I’m sure people using the system will want changes I haven’t thought of. I wrote my post because I wondered if there were any big structural limitations to the list approach.


              Erolst provided help on this score.


              Thanks for your reply, too.

              • 4. Re: Using list for relationship?

                Why not experiment with a small data set of Customers and Cohorts, and see what you can see …?


                sccardais wrote:

                I wrote my post because I wondered if there were any big structural limitations to the list approach.

                It's a nice ad-hoc approach and allows some nifty things, but when it comes to storing complex data (see above for a definition of “complex” in this respect), you'll reach its limits pretty fast – as soon as you step from the simple “a customer is in a cohort” to “a customer (is) in a given cohort (and) has …

                • 5. Re: Using list for relationship?

                  That's what I'll do. Thanks very much for sharing your knowledge so generously. This forum has so many people willing to share their wisdom and experience.


                  Sent from my iPad