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

Referential integrity quandry

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

    Referential integrity quandry

    I have a set defined in A5 containing 3 tables of customer, invoice and invoice item as per the following example:

    A. customer -- 1 to many --" invoice key is CustomerNo
    B. invoice -- 1 to many --" invoice item key is CustomerNo + InvoiceNo

    Of course when I create a new invoice item via this set I want the customer number and invoice numbers automatically maintained for me in all relevant tables. But wait, A5 has referential integrity. Great! This is why I love A5 - it does all these housekeeping things automatically for me.

    So in my A5 set definition, my table link in A. is CustomerNo of course, and my link in B. is an expression: str(CustomerNo,6,0) + InvoiceNo.

    But A5 throws up an error message if I try and select "Cascade changes/deletes" saying " ... the parent key cannot be an expression". Duh! Stymied!

    Does anyone know how I can get A5 to enforce referential integrity in this case? Many thanks.

    #2
    RE: Referential integrity quandry

    I would think the Invoice -" Invoice_Item link should be based only on the invoice number. I see no advantage to including the customer number in the link definition. After all, can one invoice go to multiple customers?

    Also, when building invoices, I recommend linking Invoice_Head to the Customer table and Invoice_Head to the Invoice_Items table. (i.e., don't start from the customer table - think of it as, "I'm building invoices not customers.") Then, if you want to build a report based on customers, filter the Invoice_Head table by the customer number.

    Comment


      #3
      RE: Referential integrity quandry

      Thanks, Cal. That's most helpful.

      Unfortunately in the example I used in an attempt to simplify things, I forgot about one thing. In my application the invoice numbers are assigned starting at 1 for each customer. So the number is not unique across all customers. Darn nuisance too. 8-(

      I have tried several times now to devise a suitable form that shows:

      A. a customer,
      B. all invoices for that customer in a browse,
      C. all invoice line items for a customer invoice in a second browse - just click on an invoice in B.

      Now to add a new invoice line item I just have to click on a new entry in C and ... oops, referential integrity problem because I need both the customer number and invoice number as a unique identifier to link the line item to the customer invoice.

      So I cannot use an expression in my set (customer number plus invoice number) linking invoices to invoice items because A5 does not allow expressions when defining a referential integrity rule.

      Also, I cannot use a single key (customer number) as the linking field plus a filter (on the invoice number) because A5 won't allow a child filter expression (invoice number) in a set to reference a field from the parent (invoice) table. And even if it did allow this I still would not get referential integrity applying to the customer number if I add a new invoice line item.

      It would be sooo much easier if A5 allowed referential integrity to apply to all fields in a link expression. It actually allows only one. 8-(

      I hope the good folk at Alpha Software enhance this sometime soon.

      Comment


        #4
        RE: Referential integrity quandry

        Brett

        Try using a key which is calculated by concatinating the fields you require Key = "Customer + InvoiceNo". I agree, single field RI sucks.

        I use the above method all the time

        John

        Comment


          #5
          RE: Referential integrity quandry

          That's interesting, John. I found one of my A5 applications where I had two numeric keys joining two tables in a set where I had defined the 1:1 linking expression as 10000*key1 + key2 just so that the result was still unique. A5 allowed me to apply referential integrity to this - well at least it did as far as I know because there is no error message.

          However, if I try and concatenate char fields in a similar fashion with referential integrity applied, I get the error message "You can only select the 'Cascade changes/deletes' option if the parent key is a field, not an expression". As I said before, duh.

          What I would like to know is:

          Q1: Are there any circumstances under which I can use more than one field to define a link between two tables in a set and apply referential integrity to this link. If so, what are these circumstances?

          Q2: Is the answer to Q1 affected by whether or not the linking fields are numeric or char?

          Q3: Is the answer to Q1 affected by whether or not the relationship between the two tables is 1 to 1 or 1 to many?

          Hopefully someone who knows can clarify this for us.

          Comment


            #6
            RE: Referential integrity quandry

            Brett

            Sorry I should have been more specific. In the primary table create a calculated table field, KEY_1 = FIELD_1 + FIELD_2 + Field_3......
            e.g.
            Field_1 Sysno C-10
            Field_2 Floor C-2
            Field_3 Door N-3

            Calculated field SYS_FLR_DO = "Sysno+Floor+str(Door,3)
            This is now your linking key.(Mixed cjar. & numeric)

            In the secondary table define your key as SYS_FLR_DO (User input. Define the individual fields as follows;

            Field_1 = Calculated LEFT(SYS_FLR_DOO,10)
            Field_2 = Caclclated RIGHT(LEFT(SYS_FLR_DOO,12),2)
            Field_3 = Calculated VAL(UT(RIGHT(SYS_FLR_DOO,3)))

            The set linking will pass SYS_FLR_DO to the secondary table & your calculated table fields will extract the individual fields.

            RI will now operate on the single key "SYS_FLR_DO"

            Hope this clarifies the confusion.

            John

            Comment


              #7
              RE: Referential integrity quandry

              Brett

              Just a further note. This is a workaround and works perfectly. The downside is that if you change a field size, you have to adjust your entire application. Not proper database design!

              I have discussed this with Alpha, but it certainly would'nt hurt for other users to also make the request. If enough users indicate this as a priority , maybe its priority will percolate up.

              John

              Comment


                #8
                RE: Referential integrity quandry

                Thanks, John. Good suggestions, I certainly try them out.

                On reflection I now think A5's inability to provide referential integrity when there are multiple keys in the linking field between tables is a MAJOR shortcoming. I have therefore posted in the Features Wish list forum (link below) a suggestion that this feature be provided. Hopefully, this will receive some attention because it will save a lot of work for serious A5 developers - those who develop comprehensive business applications.

                Thanks again.

                http://msgboard.alphasoftware.com/alphaphorum/read.php3?num=17&id=783&loc=0&offset=0&sortby=lastreply&direction=desc&thread=783

                Comment


                  #9
                  RE: Referential integrity quandry

                  Ah, yes. I see how it works. Very clever. 8-)

                  Another downside is that the child table is now made difficult to update directly because the 3 "real" fields are now calculated fields. So a form that updates these 3 fields has to break up sys_floor_do (15 characters) into its individual 3 component fields so that the individual fields can then be updated by the field rules. Too bad if we want to apply individual field rules to these 3 fields. Very messy. (I hope I have explained this clearly).

                  All this palaver is necessary because field rules apply globally to the tables on which they are defined and cannot be applied just to the set as we could in Alpha Four. But that's another story.

                  I would much prefer to see Alpha Five enhanced to allow referential integrity to apply to all the fields in a set link and not be limited to just one field.

                  Comment


                    #10
                    RE: Referential integrity quandry

                    if you made the invoice number itself the customer number plus the invoice number it would greatly simplify things.
                    Cole Custom Programming - Terrell, Texas
                    972 524 8714
                    [email protected]

                    ____________________
                    "A young man who is not liberal has no heart, but an old man who is not conservative has no mind." GB Shaw

                    Comment


                      #11
                      RE: Referential integrity quandry

                      Brett:
                      Your quandry revolves around one simple issue:
                      "the invoice numbers are assigned starting at 1 for each customer"
                      That's a no no.

                      Even if you manage to figuare out a way around RI (which is easy to do), you will have trouble, MAJOR trouble, with this design, here is why:
                      Cust# 123 Inv# 12 will have the same key as:
                      cust# 1231 Inv# 2
                      That's a big mess! These 2 customers will bring up line items that belong to each other!
                      (there is a way to get around the above, but I think you should avoid this alltogether).

                      You need to make inv# sequential across all invoices, period. Then you could apply RI and avoid this mess in the future.

                      As to RI in general and the fact that alpha does not allow expression, I am glad they do not and I hope they won't. Alpha is correct on not allowing that.

                      Think about it:
                      1-Suppose you have a parent table that have these two fields: Fname & Lname and a child table with the same fields
                      2-Let's say you want to link these two tables with an expression (Fname+Lname) and add RI:
                      3-The result of the expression for Fname:John & Lname:Smith is: "JohnSmith"
                      4-How is alpha to break this expression down at the child level? Does it break it down to John & Smith where John goes to Fname and Smith goes to Lname? or does it break it down to Joh & nsmith? or Jo & hnsmith?
                      5-You might say, well, why can't alpha maintain the original components in some holding place then it will know what to break the expression down to?
                      Then it's not an expression any longer, we are back to fields.
                      You might say, well why can't you use more than one field, not in an expression, just two un-adulterated fields, to link so as to be able to pass the values from these 2 fields from the parent to the child?

                      Well, that's very easily done in more than one way, the easiest of which is to use an alias table linked on the other field, Vola!

                      Gabe

                      Comment


                        #12
                        RE: Referential integrity quandry

                        Gabe,

                        No, sorry. I was only using my example as an analogy to make things clearer. It is certainly not the way my application is designed. I am an experienced database designer so I do not (normally) fall into such traps. 8-)
                        The main point I wished to convey was if I have a parent table PT linking a child table CT in a 1 to many relationship where there are two (or more for that matter) linking fields F1 and F2 that are primary keys and collectively the unique identifier for PT (F1 and F2 are each foreign keys for separate tables), how can I get A5 to enforce referential integrity between PT and CT on new records I might add to CT? These answer appears to be that I cannot because A5 only allows one such field. This is not good news. 8-(
                        But your suggestion about an aliased table linked to the other field intrigues me. Do I detect an effective work-around manifesting itself here? Please tell me more about your suggestion.

                        Comment


                          #13
                          RE: Referential integrity quandry

                          Brett:
                          Using an alias is an easy, but rather "primal" and, worse, if you have a big set to begin with, it will complicate matter severely.

                          Personally, I would write a simple script that carries the values from the parent to the child, you could carry as many fields as you wish and you could do the same when you change or delete.

                          Gabe

                          Comment


                            #14
                            RE: Referential integrity quandry

                            Thanks, Gabe. Yes I think am will have to do this and tie the script to a "save" button on the form. I can't think of any other durable solution. Just as well my database doesn't contain too many of these types of tables.

                            Comment

                            Working...
                            X