Multiple Addresses Challenge

[MoGrace]

In a case management app I am working on I have a scenario where it is required that for each member of a household,
records are maintained for every change of address which may or may not be different from the others in the household.

In my form want to be able to link an address in a child table that may or may not be the same as the parent address.
And if the address is the same, I do not want to create an additional address record for the child:
the condition for this would be ASAME = .T.

However, since the parent record may have multiple address records as well, then the child would need to use the same
DATA_ID field that the parent is using for that address. This is where is gets complicated for me because the
DATA_ID field right now is auto increment and would thereby be unique for each address record in the address table.

All records for parent & child are in the same table which requires 2 keys: one is shared by all family members,
so they may be grouped together, while the other is unique to the member.
Ex: Common CLIENT_ID = "C000123", related MEMBER_ID = M000123.01, M000123.02, M000123.03, etc.

For the HOH member only, a flag is set to true.

It also has to be possible to move a member to another client_id either creating a new Client_ID for him, or by
changing it to that of another HOH client member. This can be handled by NOT allowing his original MEMB_ID to
be changed, so that the combined ID fields remain unique to the table. I created a calc field KEYLINK for this.

Over time, the members of the household may vary and I have an intermediary table to help handle this.
Each HOH Client may have more than one Case linked to which ever household members are present at that time.
So for a particular case, these links may be changed from any prior ones. Also it is possible that for a period
of time in one particular case, the HOH member may not be residing at the same address as the other members
and that change needs to be noted (Ex: Mental Health facility, VA Hospital, Long Term Care facility, etc.).

It's probably obvious that I could use some design help in how to handle the many to many relationships in this app.
But if someone could help me figure out this address scenario, I would be well on my way to wrapping it up.

Somehow I need to be able to lookup the Data_ID field and assign it to a household member without losing the ability
to auto increment that field in the address table when adding a new record. This is what has me stumped...

[Ted Giles]

Re: Multiple Addresses Challenge

Having worked on very expensive case management systems, I know this is not always that easy to design.
One thing which would help would be a logical diagram of the process without the words.
Can you do one in Visio?

[DaveM]

Re: Multiple Addresses Challenge

I have come quite close to this in a church app. I did do similar in a car lot program, where the buyer is always first, and pulled buyer from existing if exists, then pull co-buyer in from existing if exists.
On the church app, I had intended to have a separate table just for addresses. Use as a lookup if exists.

[Ted Giles]

Re: Multiple Addresses Challenge

I suspect it's a lot more complicated Dave, that's why a logical 1:M diagram would help explain it.

[MoGrace]

Re: Multiple Addresses Challenge

I think my intermediary table will be the place to handle this because that is the table I use to link to multiple family members for the client. So by selecting the DATA_ID field from a lookup in that table, it will create the link I need for the child address table. Still testing...since I also have to be able to create a new address, I think the lookup field rule can accomplish that. Getting this designed in a flow chart is a bit different than getting that design to work in the UI - too many 'what if's!

[Al Buchholz]

Re: Multiple Addresses Challenge

I developed a system for paternity tests in Alpha 4 - a long time ago in a far away land....

I used tables for people, places, and family units (actual and potential).

Then built other tables to hold relationships and history of those relationships.

You always need an audit trail and history of what has transpired at all of the tables.

Definitely a lot of fun.

[MoGrace]

Re: Multiple Addresses Challenge

Al - I almost have my broken form fixed - at least enough that it can be loaded

[Ted Giles]

Re: Multiple Addresses Challenge

"Getting this designed in a flow chart is a bit different than getting that design to work in the UI"
Not a flow chart, a logical design, possibly using UML
Going down the "If Then And" route will drive you nuts.
There are only so many logical events in a M:M:M relationship, all based on a FK of some type, whether constructed or actual.
Personally, I don't find textual description half as useful as a decent diagram. Words have different meanings, diagrams rarely do.

[MoGrace]

Re: Multiple Addresses Challenge

Ok Ted, since you asked - here is my 'logical design' using Libre Office-Draw. It didn't have the right connectors so I used arrows as the 1:N link connectors. No arrow = 1:1. Also my color coding is supposed to show that there is a relationship - well sort of. Finance is not actually related to Visit. Well maybe it is, since the client's finances may have changed on a per visit basis...

CaseMgt.pdf

[Ted Giles]

Re: Multiple Addresses Challenge

Cool! Let me cogitate.

[Ted Giles]

Re: Multiple Addresses Challenge

Do you want all of the tables shown on one form?
I suggest not. (Although I have something working - badly)
The logical flow I imagine is

Add Case
.Case has primary person
..Primary person has Address(but one main one for letters etc.)
..Primary person has Contacts and type of contact (Relative, Friend etc.)
...Contacts have Address( as above)
.Primary person has Requests

.Case has Notes
.Case has Visit Diary
.Case has Vouchers
.Case has Income

So, the Case has the primary person, all their contacts, all their employers, all the addresses and their requests.

I don't know how you did it Robin, but I would use a Relationship Type for contacts (Cousin, employer).
Then you have just 4 tables, Person, Contacts, Addresses and Requests

[Ted Giles]

Re: Multiple Addresses Challenge

Having seen the application, I can see the complexity now.
Excellent design and graphics.

[MoGrace]

Re: Multiple Addresses Challenge

It seems to me I mentioned this other problem somewhere but can't find it now. I have very complex sets for the 2 main forms I have. Then needed to create a new set for the vouchers form and a variation of it to print out the voucher, using pretty much the same tables. However, when I have the form open and go to preview the voucher before printing it, I get an error that AA can't add the relationship in the set to one of the tables because it is being used in another process. The set the form is based on is open but not in change mode or anything, so why is this error happening? I tried using an alias in the set structure for the report (seems this 'worked' before...), but it is not helping now.

I am out of ideas on this one since it never happened to me in v11.

[CharlesParker]

Re: Multiple Addresses Challenge

Somehow I need to be able to lookup the Data_ID field and assign it to a household member without losing the ability to auto increment that field in the address table when adding a new record. This is what has me stumped...

I suppose you may need another related table called data_ids where you store the id and the member_id to include an auto increment then you just look up the highest value for the member which would always hold the current value. I am not a db expert so disregard if I am off base on that...This might also allow for some ID history as well if you included a date/time field.

[Ted Giles]

Re: Multiple Addresses Challenge

Can you try building it in V11 and running it, then run it in V12?
If the error is in one and not the other it is a bug and you could report it.