1 2 3 Previous Next 30 Replies Latest reply on Nov 9, 2012 10:58 AM by philmodjunk

    many-to-one relationships in one-to-many setting

    ultranix

      Title

      many-to-one relationships in one-to-many setting

      Post

           Here I am trying to set up database for my real estate activity. I use 3 tables and am in need of 4th. 3 I use are:

           1) Base (table of contacts, it would basically contain personal information: name, phone, email)

           2) Query (table of queries, as 1 contact my have many queries, because at the same time the same person can be either seller or buyer and can be a buyer of several flats/houses)

           3) Action (table of activities of the particular query: calls, views, emails, etc.)

           As for now I have those 3 tables related in the way listed below:

           BASE::id_conta = QUERY::id_conta (as one contact may have many queries)

           QUERY::id_query = ACTION::id_query (as one query may have many actions)


           What I want to accomplish is simply two things:

           1) Track how many views are for the particular object (flat/house). To achieve this i'm contemplating to add another table: OBJECTS. Therefore, I would move object_type (flat/house) to OBJECTS table and leave only id_query, id_conta, and id_object and action_type (buyer/seller/tenant) in QUERY table. I just don't know how would i squeeze in that OBJECTS table, because 1 query can only contain 1 object.

           Plus, in which table should i put that calculation of views field?

           2) Track how many buyers are contacted regarding particular object (flat/house). That is, I set a record in a queries table action_type = Buyer, add address (or object_id, if I'm able to solve the problem in 1st question, above) and count how many buyers are contacted regarding that particular object.

           This would be very helpful to automate reports that I send.

        • 1. Re: many-to-one relationships in one-to-many setting
          ultranix

               I forgot to add that Object is not mandatory for every query. Object is optional and it depends whether it exists and is known. For example, if a person contacted and only wanted to inquire about possible flats he could buy, I would only add new contact, create new query and add action with date, time, action and info.

          • 2. Re: many-to-one relationships in one-to-many setting
            philmodjunk

                 Sounds like you have a many to many relationship. See if this matches your situation:

                 A given object can be "queried" by multiple contacts in Base. A given contact in Base can query multiple objects.

                 If so, your Queries table should be set up as a "join" table between contacts (Base) and Objects.

                 Base---<Queries>-----Objects

                 Base::id_base = Queries::id_base
                 Objects::id_object = Queries::id_object

                 A portal to Queries on the Base layout can list all queries made by a given contact. Fields from Objects can be included in the portal row to supply needed info on each object queried. A portal to Queries on the Objects layout would list all queries made about that Object.

                 Report layouts can then be based on Queries that list either all queries made by a given contact or all queries made about a given object.

            • 3. Re: many-to-one relationships in one-to-many setting
              ultranix

                   Ok, that would seem like a solution, but i cannot omit ACTION table. You see, whether object exists or not, is optional, but ACTION is always present. I don't just store contacts in BASE without any actions (call, mail, meet, view) assigned and tracked.

                   That's where the question begs to be asked: how do i incorporate ACTION table?

                   If i use relationships between OBJECT and ACTION, that wouldn't be correct, as one object can be "queried" multiple times and you won't get proper action info on that.

                   ACTION is directly related to QUERY table, but if ACTION is related to QUERY table, what happens with the QUERY relation to OBJECT?

              • 4. Re: many-to-one relationships in one-to-many setting
                philmodjunk

                     If each action table is only linked to a specific query, then you would keep that relationship in place as you already have it.

                • 5. Re: many-to-one relationships in one-to-many setting
                  ultranix

                       Ok, i was able to accomplish that.

                       now the tricky part, which I failed to accomplish.

                       I need to make two calculations:

                       1) Incoming callers

                       2) Outgoing callers

                        

                       Incoming callers need to qualify for the particular conditions:

                       1) they need to be listed as BUYER in QUERY::action_type (I think it could be achieved via calculation field "is_buyer" with such expression: Case (QUERY::action_type = "Buyer"; 1)

                       2) they need to be listed as IN in QUERY::client_type (this again could be achieved as previous calculation with field "is_in" with expression: Case (QUERY::client_type = "In"; 1)

                       3) they need to have at least one contact entry with not empty info field (this could as well be achieved using similar calculation field "is_contacted" with expression: Case (Not IsEmpty (ACTION::info); 1)

                       Outgoing callers need to qualify for the same conditions, just instead of 2nd condition listed above, they need to have field "is_out" and expression: Case (QUERY::client_type = "Out"; 1)

                       But in which tables do i put each of those fields (i bet they might belong to different tables).

                       It would be great, if that could be displayed in OBJECT layout, because I already setup the total_views of particular object, so if I also have number of both Incoming and Outgoing callers, it would make my report work like a charm.

                  • 6. Re: many-to-one relationships in one-to-many setting
                    philmodjunk

                         Do you create a record in Base for each "caller"?--just checking your use of terms here.

                         Can you describe 3) in more detail? I don't know what you mean by "at least one contact entry".

                    • 7. Re: many-to-one relationships in one-to-many setting
                      ultranix
                      As for at least one contact entry - at least one associated record in ACTION table with not empty info field. As for records in BASE. Yes, each caller has a record, but i want to use QUERY table as the main source, because one contact in BASE can be a buyer for many objects (i.e. Can have many query records in Query table), so that i could clearly track which of the buyers, not excluding repetetive ones with multiple different object views are associated with the particular object. So i need 3 fields: 1) total views of the particular object (have that solved by myself already) 2) total of incoming callers (number of queries, that share the same object address (object), client type = In, and have at least one completed action record, i.e. Not empty action::info field) 3) total of outgoing callers (number of queries, that share the same address (object), client type = Out, and have at least one completed action record, i.e. Not empty action::info field)
                      • 8. Re: many-to-one relationships in one-to-many setting
                        philmodjunk

                             Why would you have a related Action record for a given query that didn't have data in "at least on info field"?

                             Keeping in mind, these relationships:

                             Base---<Queries>-----Objects
                                              |
                                              ^
                                         Actions

                             You can set up a calculation field such as cIncomingCallerFlag : Not IsEmpty ( is_in ) and Not IsEmpty ( Actions::cDataFieldFlag )

                             where cDataFieldFlag is a field that only contains data if at leaste one of the required "info" fields in Actions contains data.

                             Then sIncomingCount can be a summary field that totals cIncomingCallerFlag to provide a total number of queries that meet this criteria. You can include this field on your Object layout to show the total number of incoming caller queries for that object record.

                              

                        • 9. Re: many-to-one relationships in one-to-many setting
                          ultranix
                          Info field is the one, there i basically store the comment of the particular call/meet/view. If the info field is empty, it means that no records in action field are present (and no actions regarding that query is taken/recorded). I use info field for calculation, because it clearly defines whether action is upcoming in the future, or is already taken in the past. If action::info field is empty, that means that the action is either in the future, or already taken, but not yet documented in the database. Date.        Time.        Action.   Info 121030.    10:30.       Call.       Agreed to follow up on 121030 15:00 121030.    15:00.       Call.       Arranged to meet on 121031 11.00, confirm 10.15 121031.    10.15.       Calll.      121031.    11.00.       Meet In this example, criteria of at least one action record of 4 listed meet not isempty(action::info). That checking for isempty(action::info) really comes into use when you create new query and assign upcoming action, thus leaving action::info field empty. So in order not to count especially outgoing callers, whom i'm only planning to call and only include those, whose ive already contacted, i included that additional condition.
                          • 10. Re: many-to-one relationships in one-to-many setting
                            ultranix
                            Too bad ipad failed to leave text formatting, so without it might get messsy
                            • 11. Re: many-to-one relationships in one-to-many setting
                              philmodjunk

                                   Why not put this comments field in Actions?

                                   From what you have describedas your current setup, you can use:

                                   cIncomingCallerFlag : Not IsEmpty ( is_in ) and Not IsEmpty ( Queries::Action Info )

                              • 12. Re: many-to-one relationships in one-to-many setting
                                ultranix
                                Comments(ACTION::info field) are in actions table already. I cannot leave it in query table, because every record has date, time, type and info/comment. If i put it in query table, i would be left with only one info/comment field, where i need all of it
                                • 13. Re: many-to-one relationships in one-to-many setting
                                  ultranix

                                       another deal: for some queries there are more than one contact (for example husband and wife), so, instead of id_query being unique i would be forced to use calculation field with Max function. that's not the scary part.

                                       what's "challenging" - i had similar setting in my beta file, where i just added another field - id_alternative (in BASE database) and calculation field in base id_conditional (expression: if not is empty (id_alternative), then it would display id_conta; otherwise - id_alternative)

                                       then i linked that in such order BASE::id_condi = ACTION::id_conta

                                       That approach works O.K. with some flaws. For example, if i wanted to contact that "alternative" person, i'm not able to do that, because both are "merged" into one after such calculations and relationship.

                                       So my question here is: how to alter/change my approach in order to be able to select the person (if there's more than one) who i need to contact next and, if i wanted, to have the portal where i could see all the records in ACTION, that are associated with the particular query, despite the number of contacts assigned to that?

                                  • 14. Re: many-to-one relationships in one-to-many setting
                                    philmodjunk

                                         If you have more than one contact linked to a given query, this is no longer a one to many relationship.

                                         You could put a join table in place between Base (contacts) and Queries to implement that.

                                         You could also create a text field in Queries where you list each contact ID separated by a return. This creates a "miniature join table" inside that one field. It's less flexible than using a join table, but for cases like this where you presumably would have at most two contacts linked to a query, you may find that it works for you. You'd use the text field with multiple contactID's as your match field in the relationship linking it to Base.

                                         With either approach, I'd base the report layout on Queries--not Actions. A list view report can be set up here where you perform a find for all queries linked tor a specified object and it can then link all actions and a portal can list one or both linked contact records from Base. A portal filter can be used if you want to specify a specific contact out of the two that are linked to the Query.

                                    1 2 3 Previous Next