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

AutoIncrement Child Field in Stand Alone Form

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

  • AutoIncrement Child Field in Stand Alone Form

    I want to do something that I originally thought would be easy, but Im finding out it is a much bigger challenge than I ever imagined. Maybe Im blinded by tunnel vision and hopefully there is a relatively easy and obvious solution that I am missing. Hence, I am asking for help from the wonderful members who participate in this forum.

    I have a simple one-to-many SET that links the parent, tblMembers, to the child tblContacts, via the Member_ID linking field. The 2nd field in the child table is a two-character field labeled Contact_ID. I want Alpha to automatically enter [as in autoincrement] the next highest number sequentially for that specific member.

    For example, if Member_ID already has three contacts, when the user enters the next contact for that specific member, Alpha would automatically place 04 in the Contact ID field. And if this is the very first contact for a new member, Alpha should obviously enter 01 in the Contact_ID field.

    There is one little caveat, I would much prefer having the user enter the child record data in a stand alone form versus an embedded browse. I realize that makes this challenge slightly more complicated.

  • #2
    RE: AutoIncrement Child Field in Stand Alone Form

    A brute force approach:

    - before calling the child form, determine if any child recs exist
    - if they do, fetch through them in order
    - when you hit the last one store increment the last contact_id value and store it in a variable
    - pass the variable to the called form

    - if no child recs exist, store '01' in the variable, and pass it to the called form

    -- tom

    Comment


    • #3
      RE: AutoIncrement Child Field in Stand Alone Form

      Robert,

      Would not the use of a previous values +1 field be of help. I think you will find that when previous values are used on a child record of a set the previous value is based on the parent link not on the child order.

      Just a thought
      Michael

      Comment


      • #4
        RE: AutoIncrement Child Field in Stand Alone Form

        Table Name: tblContacts

        1st Field: Member_ID [C6]
        2nd Field: Contact_ID [C2]

        Michael wrote:

        [Would not the use of a previous values +1 field be of help]

        Don't know Michael, I haven't tried that option. Please note the question was reference entering data via a "Stand Alone Form" [only one table is open, the child], as opposed to entering data via an embedded browse.

        During data entry for a new record, the stand alone form [tblContacts] must lookup the current Member_ID[first field in the table] and ascertain if there are any Contact_ID's matching the current Member_ID.

        If there are matches, we want Alpha to lookup the LAST Contact_ID [actually the highest Contact_ID] and then add one. So if the last or highest Contact_ID for the current Member was "03", Alpha should AUTOMATICALLY enter "04" as the next Contact_ID in tblContacts.

        However, if there are NO matches for the current Member, then Alpha should automatically enter "01" in the Contact_ID field.


        Robert

        Comment


        • #5
          RE: AutoIncrement Child Field in Stand Alone Form

          If you don't care whether the contact_id is entered at the beginning o f the entry or at the end, then the problem is the same as the one in the "numbering invoice items" article at www.learn alpha.com.

          Comment


          • #6
            RE: AutoIncrement Child Field in Stand Alone Form

            Yes, I do care when the Contact_ID is entered. If it's possible, I would like the data automatically entered when the user enters the field.

            The first field is Member_ID and it is a lookup field. The user then navigates to Contact_ID where the "goal" is automatic entry of a new Contact_ID as the user then proceeds to enter all of the pertinent details about the contact.

            I checked out your "Numbering Invoice Items" article, please correct me if I'm wrong, but it appears as if the article is geared towards controlling line numbers and only in via a SET.

            In this scenario, we have to look up the Member_ID in the current table [no parent is involved] and ascertain if there are any Contact_ID numbers associated with the current member. Once again, if there are no Contact_ID numbers, enter "01", and if there are previous Contact_ID numbers, find the last/highest number and add one.

            Any additional thoughts or suggestions?

            Thanks,

            Robert

            Comment


            • #7
              RE: AutoIncrement Child Field in Stand Alone Form

              Robert, I replied to your email and your mail server bounced my message back to me. -- tom

              Comment


              • #8
                RE: AutoIncrement Child Field in Stand Alone Form

                Hi Robert,

                Wouldn't dbmax() function provide you this info? You have to have an index (probably your member id field) and it should return the largest value in any existing field for a given member_id. You could then increment it by one. An :
                if isblank("field") then
                dbmax('parameters') + 1
                else
                field = current field value
                end if

                should do this, or something close.

                Jim

                Comment


                • #9
                  RE: AutoIncrement Child Field in Stand Alone Form

                  Hi Tom and Jim:

                  Thanks so much for your suggestions.

                  Right now I'm working on a script that does almost exactly what I want. During data entry of a NEW Contact Record, the script looks up and finds the last/highest Contact_ID number for the current Member_ID. I am using a stand alone form for what is obstensibly the child table in the tblMembers to tblContacts set.

                  Please note that all of the above takes place outside of the SET. I don't know if I'm taking the right approach, maybe there is a more efficient or easier method, but I'm so close that I don't want to give up.

                  Robert

                  Comment


                  • #10
                    RE: AutoIncrement Child Field in Stand Alone Form

                    Robert,

                    This is the approach I take. In the OnWrote field rule event for MemberID put the following code
                    ' Increment the count
                    vDup = 1 ' starting increment
                    On Error GoTo Rule_Error
                    childtable->ContactID = STR(vDup,2)
                    END
                    Rule_Error:
                    vDup = vDup +1
                    RESUME 0

                    then a Unique rule on ContactID says MemberID + ContactID must be unique.

                    Requirements - must do data entry or lookup into MemberID field, or OnWrote rule will not fire. Both MembreID and ContactID must be character. Above code does a blank fill, but you can obviously do 0 fill.

                    Works like a charm.

                    Bill.

                    Comment


                    • #11
                      Apparent Solution !!!

                      Hi:

                      Thanks to some key input from others, it appears as if we have a working solution to the challenge posed. Please allow me to summarize the goal. We have a simple One-To-Many Set where the parent table is tblMembers and the child table is tblContacts, linked by the Member_ID field

                      Instead of entering NEW contacts via the embedded browse, I much prefer using a stand alone form based solely upon the child tblContacts. Beautiful, efficient form design is one of Alpha's strong points and I want to use it for data entry.

                      The problem was finding a way to automatically enter a NEW 2-character Child_ID number [autoincrement] for each contact during DATA ENTRY. I obtained the original idea for a solution from Steve. W, much needed motivation from Tom C., and I owe a great big thank you to Jim C. who editied/modified my flawed script.

                      Although there is a problem that I will describe at the end of this response, the solution seems to work well. Here is a big plus, despite searching through a large number of records, data entry is automatic and appears to be almost instantaneous because the LookupC() function utilizes an index.

                      Process:

                      The user clicks on a New Record button, a lookup for the first field [Member_ID] pops up and the user selects a Member_ID. The cursor than moves to the 2nd field called Contact_ID where the following script runs via the OnArrive Event. Here is the script that still needs to be modified.

                      Dim T as P
                      Dim VMembID as C
                      Dim VContact as C

                      t= table.current()
                      VMembID = t.member_id
                      VContact = Lookupc("L",VMembID, "Contact_ID", "tblContacts",
                      "Member_ID")

                      if VContact =" " then
                      parentform:contact_id.value = "01"
                      else
                      parentform:contact_id.value = Right("00" +
                      alltrim(str(val(VContact)+1)),2)
                      end if
                      END
                      ---------------------------------------

                      If this is the first Contact_ID for the current Member_ID, the script places "01" in the field. Otherwise the script finds the last Contact_ID number and adds one.

                      I haven't had a chance to examine Bill Parker's solution, maybe it is better and more elegant than this one. But we do have one significant problem and I'm open to any suggestions.

                      Problem:

                      The script does find the LAST Contact_ID number for the current member, but unless the Contact_ID records are sorted appropriately, the last Contact_ID isn't necessarily the highest Contact_ID. So we need to incorporate a way to sort the Contact_ID records linked to the current Member_ID.
                      Jim C. suggested using the DBMax()function.

                      The script is activated via the OnArrive Event for the Contact_ID field. Is there a more appopriate location for this script or is this OK?

                      Any other suggestions or comments?

                      Thanks,

                      Robert

                      Comment


                      • #12
                        RE: Apparent Solution !!!

                        Hi Robert,

                        I'd check the content of the field before executing the lookupc() or dbmax() function. If the field is already populated you don't want to have the overhead of running the lookup each time you enter the field if it isn't needed.

                        Jim

                        Comment


                        • #13
                          RE: Apparent Solution !!!

                          [I'd check the content of the field before executing the lookupc() or dbmax() function[

                          Excellent sugguestion Jim, in fact, I was thinking of employing a Skip .t. rule once the Contact_ID is filled in. However, this may be a better idea, let me think about it.

                          Let me tell you Jim, it feels soooooooooo good to finally have this challenge resolved and the script running as designed. But of course, once we have this script working perfectly, Bill and Tom came up with excellent alternatives so no I have 3 choices.

                          Robert

                          Comment

                          Working...
                          X