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
Devcon 2020 Goes Virtual! LEARN MORE >

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

Help in linking many tables, sets/lookups

Collapse
X
Collapse
 
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
Previous template Next
    #1

    Help in linking many tables, sets/lookups

    Hi,
    I'm building up an application and don't know how to deal with a contract form problem.
    It is a rental contract for a tool rental system.
    Info frm the following tables have to be used:-
    a)Client name - name, id, etc.
    b)Cliente firm - firm name,address, etc
    c)Machine list - machine details, serial num, etc.
    d)Accessories list - accessories provided with that type of machine.
    e)Various times and dates, etc.

    I want to enter only the client number, then the number of each machine to be rented on to the contract form.

    I think I have sorted out (a)(b) by using a set, and (e)by using calculated fields on the main form, etc.

    My problem is (c) and (d):-
    Each contract can have up to three machines, each machine has it's own individual number.
    Each machine is classed as a type of machine, each type of machine has it's applicable accessories.
    Thus (c) and (d) may be used 3 time each, but with
    -If I use a look-up, the machine can get looked up, but without entering manually the machine type the applicable accessories won't be looked up. A5 does not seem to be able to use a non-manually entered field to do a lookup.
    -If I use sets, I will be using the same child table three times in the same set, which I am told is not a good thing, and certainly A5 doesn't like it.
    Thus, as I see it, I can't use lookups nor sets. So I am baffled for now.

    I'm sure I am missing something stupid, and am certainly new to this. I think that there is a concept I am not getting.
    Any help would be appreciated.

    Brian
    Tags: None
    #2
    RE: Help in linking many tables, sets/lookups

    Brian,

    If you are just using lookups from other tables in the main form you
    don't need a set.
    efs

    Comment

      #3
      RE: Help in linking many tables, sets/lookups

      Hi Edward,

      Thanks for the response.
      I am not trying to just use lookups from the main form.
      I want to type in four numbers: the client number, then three machine numbers. Then the form should be completed with the following:-
      1)The client number should do the following- provide the fields of client details (complete name, id numbers,etc)
      via a linking set it will also provide details of firm(name, address, tax number, vehicle plates, etc)
      2)Each machine number will provide details of the machine rented, (type, model, serial number, etc), and via set or lookup will provide info of accessories relevant to that type of machine, (safety equip provided, accessories for that type of machine, etc.)

      There may be three different machines per contract, they may be different or the same type of machine. They will be different machines.

      I see the problems as:-
      - In a set, I should not use the same child table more that once.
      - Lookups need input from the keyboard to the linking field to work. (eg inputing from a variable to a linking field does not allow the lookup to work)
      - Lookups only work on tables, not on sets.

      I guess using lookups would be better, in order to maintain a finished record of the contract that would not change in the future if items in the individual tables were to change.
      Using a set would not give that permanentcy.

      Thanks for the help,

      Brian

      Comment

        #4
        RE: Help in linking many tables, sets/lookups

        Why not put a button on the form, that's labelled, 'Retrieve details'. Require the user to supply the four pieces of informaiton , then use the button to run a script which opens the tables, sets the desired index, and does a fetch_find() to retrieve the values you need. Assign each to the desired field object on the form and the user thinks you work magic.

        -- tom

        Comment

          #5
          RE: Help in linking many tables, sets/lookups

          For c and d you need another set that links your machine list table to the accessories list table in a 1 to many link. Look up the machine in the parent of this list and display the machine name etc as you did in your solution for a and b. Then place a browse or browses on your form that display(s) the items of the accessories table. Now when you choose a machine, the accessories for that machine will show up in the linked browse(s).

          You will not be able to print this directly however since A5 doesn't print forms with 1 to many links very well. Instead you need to create a report that shows the items listed in the browse in a subreport.

          Hope this is of some help.
          Ohlen

          Comment

            #6
            RE: Help in linking many tables, sets/lookups

            Hi Tom,
            Thanks for the help.
            Sounds like the best thing available. I am with you up to the point of "Assign each to the desired ...." Do you mean using variables to copy from the relevant tables to the field objects on the form, or is there an easier/(quicker, in the performance viewpoint) way of assigning those values?
            There may be approx 15 fields average to be copied from each record, x 8 records, (each of those 4 has a "child") ie. setting variables and updating fields x 134.
            Should I be looking at reports as a way of doing this, or do you think I will be running into the same problems in a different way?
            As always and often, thanks for the help.

            Brian.

            Comment

              #7
              RE: Help in linking many tables, sets/lookups

              Ohlen,
              Thanks for the response,
              I think I see what you are suggesting. As I understand it, I make a set, machine table-1:n-linktable-1:1-accessories table. Use a lookup from the contract form to lookup the machine table, which then will display the accessories in an embedded browse.
              I think I tried something like this before, got the browse displayed, but couldn't get the info displayed in the browse to the correct positions in the form, nor into the fields I wanted filled on the form.
              Haven't tried reports yet in A5, guess will be going in that direction as will need to print out forms of 1:n often.
              Thanks for the help.

              Brian

              Comment

                #8
                RE: Help in linking many tables, sets/lookups

                i am at a loss as to why you need all the sets.
                you need 4 tables
                customer
                machine
                contract_header
                contract_detail
                use one set
                contract_header and contract_detail

                contract_header
                contract_ID
                customerID
                customer name
                address1
                address2
                city
                state
                zip

                contract_detail
                contract_ID
                machine_num
                machine_desc
                add as many fields that you need.

                link these by contractid
                the header would do a field rule lookup to a customer table based on customerID and fill in the name, address,city state

                the detail would do a lookup on the machineid in the machine table
                and fill in the machine info

                charlie crimmel

                Comment

                  #9
                  RE: Help in linking many tables, sets/lookups

                  Charlie,
                  Thanks for the response.
                  The problem is because the contract is renting up to three machines.
                  The client part is not a problem.
                  The problem is the machine part.
                  I agree that your idea would work if it were only looking up from the machine table. The situation is not that.
                  Each machine is unique with eg it's serial number. Each machine is also a type of machine.
                  Each type of machine has accessories.
                  Eg: an electric hammer has machine number 400, and type number 60.
                  We may have 20 electric hammers, each with different machine numbers, each of type number 60. There may be 10+ accessories to each machine.
                  To avoid a very large table for each machine, all of which would have to be changed if eg, we included protective glasses in that type of accessories, I have a machine table and an accessories table.
                  Putting that into a set is easy if only only machine per contract.
                  Putting three+ machines onto one contract is easy; if not have accessories; using lookups. But lookups don't work with sets.
                  I think I need a function which will enable me to input a value to a field from a variable, and that can then be used as a trigger for a lookup; or a work around which does that effect.
                  Thanks for the help.

                  Brian.

                  Comment

                  Previous template Next
                  Working...
                  X