PDA

View Full Version : Alias Join Table and How to use Type Fields


ABC123

netgeorger
04-09-2008, 06:42 AM
In Approach they had what they called an alias join database which was a virtual table, where no data was kept but which allowed a way to join a mirror image of a database to itself. They used the classic example of the Employee and Manager. Employees have managers but Managers are also employees both require the same data entities. Thus the original Employee table would have an Employee Id and a Manager Id then they would use the Alias join to be able to key the Employee table as a Manager table. I have the same sort of situation with clients and contacts . How can I simulate the same sort of thing in Alpha or is it possible? How does one go about using more than one field as join fields in Alpha? Such as combining fields to make a primary key of two fields?

Another question I have is if I use a “type field” for entities rather than making up 27 or more tables for each type of entity. How do I manipulate the data which is intrinsically related to the type? The example I might offer is that of date fields say I have quite a number of different types of date fields Birth date Death date, Start date, End date, filing date , fee agreement date, Probate date. Marriage date, Divorce date, Trial date, date of death etc. Obviously many of these values would be empty or null for any given client´s information. So to avoid nulls I simply make a table with client Id and Date Id then a Date type field and Date field. Now I have a clean table with, the name of the kind of date and the date. no nulls. Unfortunately one doesn´t mean much without the other and I need to manipulate the dates. Put them in functions etc and embed them in documents how can I do this using the combination of Date Type field and Date? At the moment I can only think of setting the value of a variable based on the condition of the Date type field and then using the variable for my manipulations ? Is this how to use this ,"Type field" or is there a more direct way? I am still trying to learn basics.
Thank you in advance for any insight or comment
George R. Kenney

MoGrace
04-09-2008, 09:08 PM
Hi George,
If a Contact can become a Client then it makes sense to put all the names in the same table and just change a TYPE field when a contact becomes a Client. Then you can filter the records to display on a form or in a report based on the TYPE.

The dates are a bit different though if they all are needed for the same record but dependent upon a particular scenario. This sounds like a legal practice? Perhaps a type of service field could be used and based on that value, you can have other tables that link the related data to the client with just the pertinent information stored. It might depend on how many different types of service are to be offered, and the forms and reports needed based on that service. After all a probate case is not like a divorce case so a date of death field would only apply to the former and not the latter (hopefully!).

Whereas date of birth and fee agreement date would be related to all the Clients and should probably be part of that table.

DaveM
04-10-2008, 03:45 PM
george,

You can connect a table two times on different keys by alias in one set. My names table is connected to inventory one to one by way of stock for the sold vehicle and also to inventory by acct on a one to many aliased "trades".

This way, I can enter a sale, the vehicle sold and any number of trades to that deal.

DaveM
04-10-2008, 03:55 PM
Another question I have is if I use a “type field” for entities rather than making up 27 or more tables for each type of entity. How do I manipulate the data which is intrinsically related to the type? The example I might offer is that of date fields say I have quite a number of different types of date fields Birth date Death date, Start date, End date, filing date , fee agreement date, Probate date. Marriage date, Divorce date, Trial date, date of death etc. Obviously many of these values would be empty or null for any given client´s information. So to avoid nulls I simply make a table with client Id and Date Id then a Date type field and Date field. Now I have a clean table with, the name of the kind of date and the date. no nulls. Unfortunately one doesn´t mean much without the other and I need to manipulate the dates. Put them in functions etc and embed them in documents how can I do this using the combination of Date Type field and Date? At the moment I can only think of setting the value of a variable based on the condition of the Date type field and then using the variable for my manipulations ? Is this how to use this ,"Type field" or is there a more direct way? I am still trying to learn basics.


I might would have this on a set with a main table where id is used for the link to the dates table(1 to many) where the fields were id, dateof, reason. Reason in turn would have a lookup to a simple table with the 27 reasons for the date being entered. The lookup's main reason would be for keeping all the answers the same("court date" is not the same as "date for court").

id would give you all the dates for that person with the reason when going to a report/browse on a form/etc.

example": You can then make up a report of all mariage dates and have the peoples name(s), etc as you need them. Or a report with names and all dates for that person grouped. The abilities are then somewhat endless.

netgeorger
04-11-2008, 02:03 PM
Thanks Robin
I am trying to build a database for my sisters law parctice and one is doing nothing but moving names into assignments and relations there are about 24 different assignment types and 27 or so relation types so it seemed to be the best way to handle them but I still end up not knowing how to move a particular name into a generic field.

netgeorger
04-11-2008, 02:09 PM
Thanks Dave
This is what I am looking for. I am still not to clear on how to do it but at least you have given me the idea it can be done. how do you set up the double join in a set? I suppose is what I am trying to figure out. Thanks again.
George R.Kenney

Stan Mathews
04-11-2008, 02:14 PM
how do you set up the double join in a set? You perform the steps to link the parent to the child, twice.

Since the second link can't have the same definition, Alpha uses an alias.

DaveM
04-12-2008, 01:01 PM
There is (as stan referenced) a place to put you alias name when you set up the link.

MoGrace
04-12-2008, 05:04 PM
Thanks Robin
I am trying to build a database for my sisters law parctice and one is doing nothing but moving names into assignments and relations there are about 24 different assignment types and 27 or so relation types so it seemed to be the best way to handle them but I still end up not knowing how to move a particular name into a generic field.If you are going to use a generic table then you need to decide how many different date fields each of the assignment-types might need to determine the maximum date fields needed. And whatever generic field names you give them, you would have to remember what they are representative of in each of those scenarios. It could get way complicated from a developing point of view.

In your forms the field labels you give those fields would clear it up for data entry, but you will still need to create report layouts. What you could do is also add fields which describe the dates entered so they are entered together based on the assignment type you select.

Some sample data would help here...

netgeorger
04-15-2008, 12:19 PM
Stan
Thanks this is the trick I had seen this someware but didn´t know how it was done.
seems obvious now.

netgeorger
04-15-2008, 12:42 PM
Robin
Thanks for your insight your right it looks good on paper to me but in practice it is too complicated much easier just to name the date fields and leave the nulls. As it is I need to manipulate each date in someway. I am not just interested in listing dates by types or something which I suppose is the main reason people use databases. I need to be able to get at each individual data just as if it were a field named object.

DaveM
04-16-2008, 11:29 PM
George,

If I were going to have assignments with dates, I wouuld think about one id field to connct this child to the client, one date field and an assignment field. Only one of each, with a lookup on the assignment field for my 3 to 500 assignments. the assignment table would only have the assignments in it as the lookup table. That way you can sort/filter easily on the date field and sort/filter easily by assignments or both.

If you find a few more assignments you need later, just add to the lookup/assignment table.

If you have a bunch of date fields and assignment fields, it is a bit simpler(at first) to get a form built, but when you try to include all those fields in a report and fileter them. OH BOY!

I did a school app one time and made lots and lots of fields. Later I rewrote that sucker. It was so much easier the second time. It had to do with about 14 subject areas and I had a date for each and a grade for each plus a space for text for each. It was horrible. It got done and Alpha4v3 did it. It worked and out performed the same app written in file maker.

Hope I am understanding your desires. If so there is a very small app below to show what I mean.

netgeorger
04-19-2008, 06:50 AM
Dave
Thanks so much for your consideration and efforts
Trying to use a type field, I don't see how I can actually get to a specific piece of data that represents a specific type to put into a document. I am still thinking like a spreadsheet or a merge file which is what I have experience with. I keep thinking that I really need something that is a multivalued field with its absurd 1 field 2 field 3 field scenario, but at least I can easily place those in a document. I need to be able to put the name of an individual into a field named as the relation roll he represents one of 27 types or the assignment roll he represents 7 of 23 possible. the same for date types one of maybe 23 or telephone types maybe one of eight types.
I can see the construct of this or treatment of flattened multivalued fields in Database Design for Mere Mortals (https://www.amazon.com/dp/0201752840/ref=as_li_ss_til?tag=131369-20&camp=213381&creative=390973&linkCode=as4&creativeASIN=0201752840&adid=1DF9PJ2J3N663MBRZP6N&) but not how you can use it other than to make a list.

As it is I have a fK of client_id, and then the Pk as date_Id
assignment_Id or relation_Id etc depending on the table.

Do I make a composite key out of just the foreign key and table_Id
for the set or do I include the type field as well as date in the composite key or not?
I wonder if it is possible to construct a single record made out of many records from many different tables keyed by client Id?
I wonder about this because it seems to me to be a way to pre filter all the data collected in individual records put them
into a single record and then use that on a letter or send it to Word or Word perfect for a merge.

I have a hard time trying to see how a generic data type such as <date>, <assigned> or <relation> can be made specific in each sentence
in a document as there may be several throughout the document.

You talk about filters this seems tricky can a filter be set up for a specific field that is placed on a sentence of a document
but which changes in another sentence such that a different filter would be used for that sentence?


Thanks again
George R. Kenney

Tom Cone Jr
04-19-2008, 08:51 AM
George, here's a simple example that may help.

Unzip the attachment to an empty folder.

Form1 illustrates a typical method for displaying multiple records that are all linked to a single primary (parent) record. It's accomplished by basing the form on a two table set, using a one to many link. If this concept is foreign to you review the User Guide sections on set design.

Here the primary table is "clients". The child (linked) table is "Phones". Each client can have more than one phone (don't we all!). Each phone is (or can be) of a different "type". The form forces the user to pick a "type" of phone each time a new phone record is entered. They pick from a list that's displayed from records in a 3rd table. A supporting or "lookup" table. This supporting table is not part of the set design.

The mechanism which presents the list of phone types to the user is defined in field rules for the Phones table. It's called a table lookup field rule. Check the user guide sections on field rules and study the topics there dealing with table lookups. You'll find lots of options are available to you.

While my example deals with "types" of phones, it could easily be adapted to "types" of dates, or "types" of cars, or "types" of movies, etc.

Hopefully this will help to get you thinking in "Alpha Speak". Holler if you have questions.

--- tom

DaveM
04-19-2008, 12:57 PM
George,

There are several tables in you set that don't make sense to me. may I suggest you upload the complete app with sample data for us to look at. Alpha's sets can be used very well, but not all things need to be in seperate tables. Relations seems like a good possibility for a one to many connection/link. You are connecting your main table in your set to your main table as I see it and unless a client has multiple addresses, why have a second table? Stuff like that. so sample data is important.

Tom gave good advice as he always does.

netgeorger
04-22-2008, 12:21 PM
Tom
Thanks so much for the zip I didn´t have any problem with this and can see an advantage over a drop down list box in that the user can have access to add or subtract from the list. I finally figured out how to use a branch to select the birthday and get the age out of just the date field.
Thanks alot George