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.
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::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.
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?
If each action table is only linked to a specific query, then you would keep that relationship in place as you already have it.
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.
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".
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)
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:
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.
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.
Too bad ipad failed to leave text formatting, so without it might get messsy
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 )
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
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?
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.