Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Alias Join Table and How to use Type Fields

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    Alias Join Table and How to use Type Fields

    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

    #2
    Re: Alias Join Table and How to use Type Fields

    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.
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

    Comment


      #3
      Re: Alias Join Table and How to use Type Fields

      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.
      Last edited by DaveM; 04-10-2008, 03:01 PM.
      Dave Mason
      [email protected]
      Skype is dave.mason46

      Comment


        #4
        Re: Alias Join Table and How to use Type 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.
        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.
        Dave Mason
        [email protected]
        Skype is dave.mason46

        Comment


          #5
          Re: Alias Join Table and How to use Type Fields

          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.

          Comment


            #6
            Re: Alias Join Table and How to use Type Fields

            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

            Comment


              #7
              Re: Alias Join Table and How to use Type Fields

              Originally posted by netgeorger View Post
              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.
              There can be only one.

              Comment


                #8
                Re: Alias Join Table and How to use Type Fields

                There is (as stan referenced) a place to put you alias name when you set up the link.
                Dave Mason
                [email protected]
                Skype is dave.mason46

                Comment


                  #9
                  Re: Alias Join Table and How to use Type Fields

                  Originally posted by netgeorger View Post
                  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...
                  Robin

                  Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

                  Comment


                    #10
                    Re: Alias Join Table and How to use Type Fields

                    Stan
                    Thanks this is the trick I had seen this someware but didn�t know how it was done.
                    seems obvious now.

                    Comment


                      #11
                      Re: Alias Join Table and How to use Type Fields

                      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.

                      Comment


                        #12
                        Re: Alias Join Table and How to use Type Fields

                        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.
                        Last edited by DaveM; 04-16-2008, 10:51 PM.
                        Dave Mason
                        [email protected]
                        Skype is dave.mason46

                        Comment


                          #13
                          Re: Alias Join Table and How to use Type Fields

                          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 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

                          Comment


                            #14
                            Re: Alias Join Table and How to use Type Fields

                            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

                            Comment


                              #15
                              Re: Alias Join Table and How to use Type Fields

                              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.
                              Dave Mason
                              [email protected]
                              Skype is dave.mason46

                              Comment

                              Working...
                              X