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

expression to create a database field

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

  • expression to create a database field

    I Am New Convert To Alpha5 From Alpha4. Having Used Alpha4 For About Fifteen Years (a4v4 Through A4v8)

    In A4, A Parent Db Linked To A Child Db In A Set, Can Summarize A Num Field In Child Database Or A Numeric Summary Can Also Appear As A Logical Field. I Used Both Under Different Circumstances. The Example In Alpha Sports Seems To Be A Logical Field Only.

    In Alpha5v8 I Have Structured A Payroll Set, Linking "employee" As Parent To A Child Table "payroll".
    A Field In Parent (employee) Has A Num Field " Salary_total",
    A Corresponding Field In Child Db Is Named "gross_salary".

    I Need To Summarize "gross_salary" And Have The Parent Num Field "salary_total" Collect Or Capture The Data In That Particular Field. I Searched For Specific Help On This But Have Not Been Able To Find It.

    Also In This "payroll" Table, I Need Once A Week To Update Two Fields..
    The First Is A "pay_date" Date Field And The Second Is A character field "check_nbr" . Both Require User Input..
    In Alpha4, I Created 2 Scripts That Used The "waitkey" Command Allowing User To Insert A Date In One Script And A Starting Check Number In The Second Script.
    In Alpha5 I Have Played With Both Of These As Buttons On The Weekly Payroll Form.
    The Date Works If I Use The Expression {date + 7}, But I Am Unhappy With This Clumsy Solution.

    In Alpha5, Can These Be Combined In One Expression And Be Processed At The Same Time??

    Would Greatly Appreciate Resolving These Matters.

    John Linley
    Last edited by john linley; 08-16-2007, 11:26 PM. Reason: to add "a character field"

  • #2
    Re: expression to create a database field

    Originally posted by john linley View Post
    I Need To Summarize "gross_salary" And Have The Parent Num Field "salary_total" Collect Or Capture The Data In That Particular Field.
    This can be done with a tablesum() expression.
    Also In This "payroll" Table, I Need Once A Week To Update Two Fields..
    The First Is A "pay_date" Date Field And The Second Is A character field "check_nbr" . Both Require User Input.. I would suggest having two variables defined for a form where the user did the input. Then an update operation would reference the values in the variables.
    There can be only one.

    Comment


    • #3
      Re: expression to create a database field

      to: stan mathews. Thanks for the very fast response. I also am an early riser.
      I will give them both a shot and hope I can succeed. Many years ago - 1950-1952 I was stationed at Fort Campbell. I guess it is still there.
      Regards and thanks,

      John Linley

      Comment


      • #4
        Re: expression to create a database field

        to: Stan Mathews

        Much appreciate your AM response. I also am an early riser.

        Will give both a try later today. Years ago, 1951-1953, I was stationed at Fort Campbell, I guess it is still operational.

        Thanks again, I will let you know how I make out.

        John Linley

        Comment


        • #5
          Re: expression to create a database field

          to: stan mathews

          Dear Stan. Updating check numbers and dates working quite well now.

          Many thanks for your help.

          Unfortunately, I am still having trouble with TABLESUM() FUNCTION !

          Alpha5 help gives the following expressions as an example.

          ? TABLESUM(PURCHASE.DBF", ".T.", "COST") = 27123.10000

          I re-wrote the expression as follows:

          VENDORS->PURCH_TOT.DBF = TABLESUM( "PURCH-PAYMENTS.DBF", "SUP_CODE","AMOUNT") = 100000.20

          I REPLACED THE ? WITH PARENT NAME AND FIELD
          I REPLACED THE FILTER ".T." WITH THE SUPPLIER CODE FIELD

          THE RESULT WHEN I PLACE THIS CALC FIELD EXPRESSION IN THE PROPERTIES BOX, I DO NOT GET A SUMMARY BY VENDOR, I GET THE NAME OF THE EXPRESSION ITSELF.
          WHEN I LEFT THE FILTER ".T." IN AT FIRST, I GOT THE TOTAL FOR THE ENTIRE DATABASE, AND NOT FOR AN INDIVIDUAL VENDOR, WHICH I NEED.

          I'M OBVIOUSLY DOING SOMETHING WRONG!! BUT WHAT ?

          JOHN

          Comment


          • #6
            Re: expression to create a database field

            I am not clear what you are attempting with

            VENDORS->PURCH_TOT.DBF =
            You cannot assign the result of an expression to "VENDORS->PURCH_TOT.DBF".

            The example in the documentation uses a static logical value in the filter portion and it is quoted. If you use a dynamic value you must supply a filter to return a logical value dynamically. From your example

            Code:
            TABLESUM( "PURCH-PAYMENTS.DBF", "SUP_CODE","AMOUNT") = 100000.20
            it seems what you wanted was

            Code:
            TABLESUM( "PURCH-PAYMENTS.DBF", "SUP_CODE = "+quote(SUP_CODE),"AMOUNT") = ?????
            Code:
            "SUP_CODE = "+quote(SUP_CODE)
            must resolve to a logical value. You can experiment with this in the interactive editor until you get it right.


            If SUP_CODE is a character field. The first instance of SUP_CODE refers to the SUP_CODE in the PURCH-PAYMENTS table. The second instance of SUP_CODE refers to the current value of the SUP_CODE field in the PURCH_TOT table. Breaking out of the quoted filter portion and concatenating (+quote(SUP_CODE)) tells Alpha to use the current contents of the field.

            Please also note that "PURCH-PAYMENTS" does not conform to recommended table naming conventions.


            Table and Field Names

            Alpha Five recommends that table names, field names, and paths to your files start with a letter (A-Z or a-z) and be composed of letters, numbers (0-9), and underscore (_) characters.
            No mention of dashes there.
            There can be only one.

            Comment


            • #7
              Re: expression to create a database field

              to: Stan Mathews;

              Good evening Stan;

              The aforementioned purch-paymnts was a typo error. I should have typed
              purch_paymnts with the appropriate underscore. Sorry for the error and inconvenience. I will try and edit my messages more carefully.

              Still having trouble with tablesum() function!! After repeated attempts and many variations I still, unfortunately, need help.

              LET ME GO TO A DIFFERENT SET!

              The set name is "INVOICING" with the parent table named "INVC_HDR" and a child table named "ITEMS_INVCD"

              The linking field is "INV_NO" The child index is "LI_INV_NO"

              A numeric field in the parent table is "INVC_TOT"

              A CALCULATED numeric field {QTY*PRICE} in the child table is "ITEM_EXTENS"

              I WROTE THE FOLLOWING EXPRESSION IN THE CALCULATED FIELDS BOX; IS THERE SOME OTHER PLACE TO WRITE THIS CODE? AS SOON AS I CLICK ON ok, AND RE-OPEN THE XY CALCULATED FIELDS BOX, THE EXPRESSION I JUST WROTE IS NOT THERE??? ANYWAY, THIS IS THE EXPRESSION...


              TABLESUM ("ITEMS_INVCD.DBF" , "LI_INV_NO = +(QUOTE(LI_INV_NO) ,
              "INVC_TOT") = 100000.20


              This expression, when I finally get it right, is supposed to summarize
              multiple invoiced line items in the field "ITEM_EXTENS" and place that summary value in the parent field "INVC_TOT"

              THE FIELD LENGTH OF THE NUMERIC FIELD "INVC_TOT" IS 9 WITH 2 DECIMALS. A SINGLE INVOICE CAN EXCEED 100,000.00

              That is why I put such a large number after the = sign. I don"t know if this arbitrary number can be shorter than the field length.


              Other tables in this set are a CUSTOMER.DBF and an INVENTORY.DBF table.
              Customer is linked to the parent by a customer code field and the inventory table is linked to the ITEMS_INVCD table by a part number field. Both are one to one links.

              Regards, and thanks for your help and patience.

              john linley

              Comment


              • #8
                Re: expression to create a database field

                Code:
                TABLESUM ("ITEMS_INVCD.DBF" , "LI_INV_NO = "+QUOTE(LI_INV_NO) ,
                "INVC_TOT") = 100000.20
                Would yield a logical T/F result.... Is the result of the tablesum() = 100000.20?

                The set name is "INVOICING" with the parent table named "INVC_HDR" and a child table named "ITEMS_INVCD"

                The linking field is "INV_NO" The child index is "LI_INV_NO"

                A numeric field in the parent table is "INVC_TOT"

                A CALCULATED numeric field {QTY*PRICE} in the child table is "ITEM_EXTENS"

                TABLESUM ("ITEMS_INVCD.DBF" , "LI_INV_NO = "+QUOTE(LI_INV_NO) ,
                "INVC_TOT")
                I think you want

                Code:
                TABLESUM ("ITEMS_INVCD.DBF" , "LI_INV_NO = "+QUOTE(INV_NO) ,
                "ITEM_EXTENS")
                Sum the values in the ITEM_EXTENS field in the ITEMS_INVCD.DBF where the value in the LI_INV_NO field in the ITEMS_INVCD.DBF matches the value in the INV_NO in the INVC_HDR table.

                I WROTE THE FOLLOWING EXPRESSION IN THE CALCULATED FIELDS BOX; IS THERE SOME OTHER PLACE TO WRITE THIS CODE? AS SOON AS I CLICK ON ok, AND RE-OPEN THE XY CALCULATED FIELDS BOX, THE EXPRESSION I JUST WROTE IS NOT THERE??? ANYWAY, THIS IS THE EXPRESSION...
                Not sure what you mean. You need to define the INVC_TOT as calculated in the parent table field rules and give it the expression


                Code:
                TABLESUM ("ITEMS_INVCD.DBF" , "LI_INV_NO = "+QUOTE(INV_NO) ,
                "ITEM_EXTENS")
                There can be only one.

                Comment


                • #9
                  Re: expression to create a database field

                  Originally posted by Stan Mathews View Post
                  Code:
                  TABLESUM ("ITEMS_INVCD.DBF" , "LI_INV_NO = "+QUOTE(LI_INV_NO) ,
                  "INVC_TOT") = 100000.20
                  Would yield a logical T/F result.... Is the result of the tablesum() = 100000.20?



                  I think you want

                  Code:
                  TABLESUM ("ITEMS_INVCD.DBF" , "LI_INV_NO = "+QUOTE(INV_NO) ,
                  "ITEM_EXTENS")
                  Sum the values in the ITEM_EXTENS field in the ITEMS_INVCD.DBF where the value in the LI_INV_NO field in the ITEMS_INVCD.DBF matches the value in the INV_NO in the INVC_HDR table.

                  Not sure what you mean. You need to define the INVC_TOT as calculated in the parent table field rules and give it the expression


                  Code:
                  TABLESUM ("ITEMS_INVCD.DBF" , "LI_INV_NO = "+QUOTE(INV_NO) ,
                  "ITEM_EXTENS")

                  Comment


                  • #10
                    Re: expression to create a database field

                    To: Stan Mathews

                    Stan; I did reply via Email, but apparently it was not the right thing to do. I have to assume you never received it. To summarize, re: the lost taskbar,
                    Your suggestion to uninstall Alpha5 and re-install solved the problem. Alpha5 is functioning properly now. I also had to unistall alpha4 and re-install. It seems that when my workstation had a hard drive failure, it affected both alpha4 and alpha5. I was in A4 at the time of the crash.
                    Alpha4 is in a nine station network on drive "H" ; alpha5 is on drive "C" on my workstation only. My workstation hard drive failure affected both A4 and A5 and only on my work station. Alpha 4 continued to work smoothly in the other 8 workstations.

                    Sometime, in the near future, when I am ready to switch from alpha4 to alpha5, I will need to increase my license count by eight and network A5. I may need both A4 and A5 to run simultaneously for perhaps two or three months. I suspect A4 and A5 need to be in different networks. Is this correct??

                    My goal is to be ready by Jan 1, 2008.

                    Re: the tablesum() . I was trying, as you pointed out, to place that expression in the wrong place. The "set field rules" is the place, not the "form calculated fields". In A4, summarizing a child table is also a set field rule. It now seems foolish of me not to realize this sooner. Your thumbnail finally cleared that problem for me. Fortunately, there are many similarities between A4 and A5. Where this is the case, I make progress more quickly than when I get into strange waters, where procedures require a different approach.

                    Thanks again for your help. You are keeping my self-inflicted jan 1 deadline somewhat realistic.

                    On another and perhaps last issue. I would like to prevail upon you for some
                    assistance for the following issue.

                    In an 'accounts_receivable.set' ; a 'customer.dbf' is linked to a "sales.dbf" on a "cust_id" field.

                    We also join in a set named 'collections.set' a 'deposit.dbf'' to the same 'sales.dbf'. The linking field here is: "check_nbr". In both sets, 'sales.dbf' is the child.

                    In the 'collection.set' manual entry in the header->deposit.dbf might be as follows:

                    date field deposit_date 09/15/2007
                    char field 6 cust_id ABC
                    char field 7 chk_nbr 4567
                    num field 9 - 2 chk_amnt 100000.00

                    That check amount usually pays at least 100 invoices (records) in the 'sales.dbf' The invoice numbers are listed on the check stub in sequential order but has some skipped invoice numbers. As an example, if customer is paying invoices 3000 to 3120, there usually are several skipped numbers between 3000 and 3015. Also there usually are skipped numbers between 3100 and 3120. Between, there might be 80 or 90 sequential invoices paid without skipped numbers.

                    The 'sales.dbf' already has the cust_id value "ABC" and the invoice numbers which is also already filled perhaps 1000 to 4000. And each record also has
                    an 'invoice_amount' field which also contains values.

                    The 'sales.dbf' fields that need to be filled are:

                    date field payment date same as above
                    char field 7 check_nbr 4567
                    char field 1 paid_y_n "P"
                    num field 9 - 2 applied_payment (same value as
                    'invoice_amount' field)

                    In A4 - we globally update the 'sales.dbf-> check_nbr' field with the value 4567 into as many unskipped sequential fields as possible, then, manually update the remaining skipped fields. With this information in place , we can then - in one pass - globally update the remaining 3 fields.

                    I suppose, we can do the same in Alpha5, but, is there a better or easier way than this to accomplish the same task ???

                    Hope you have a better idea on this.

                    Regards and many, many thanks;

                    John Linley

                    Comment


                    • #11
                      Re: expression to create a database field

                      John, over the years I've learned that the message board works best if each thread is limited to a single question or situation. Folks with similar questions can find and follow the threads much more easily later on. Taking a thread off topic with a new question also defeats the automatic search for similar threads that happens each time the thread is viewed, since that's based on the original topic subject heading. It's not my intention to be critical here, just asking (on behalf of others who will come along later) that you consider this next time.

                      Comment


                      • #12
                        Re: expression to create a database field

                        To: Tom Cone, Jr.

                        Thank you for the advice. It does make sense, and I will certainly heed it in the future. I am not only a new convert from Alpha4 to Alpha 5, but I am also a new user in the internet as well as the Alpha Messgae Board, which, thanks to more advanced and competent users I have been helped a great deal.

                        I will try to open a new post (thread?) on this different topic. It seems as though I have too many personal notes entwined into my replies, such as this one. Can I remove them without destoying the more pertinent question and answer.?
                        Thanks again, John Linley

                        Comment


                        • #13
                          Re: expression to create a database field

                          Originally posted by john linley View Post
                          to: stan mathews

                          Dear Stan. Updating check numbers and dates working quite well now.

                          Many thanks for your help.

                          Unfortunately, I am still having trouble with TABLESUM() FUNCTION !

                          Alpha5 help gives the following expressions as an example.

                          ? TABLESUM(PURCHASE.DBF", ".T.", "COST") = 27123.10000

                          I re-wrote the expression as follows:

                          VENDORS->PURCH_TOT.DBF = TABLESUM( "PURCH-PAYMENTS.DBF", "SUP_CODE","AMOUNT") = 100000.20

                          I REPLACED THE ? WITH PARENT NAME AND FIELD
                          I REPLACED THE FILTER ".T." WITH THE SUPPLIER CODE FIELD

                          THE RESULT WHEN I PLACE THIS CALC FIELD EXPRESSION IN THE PROPERTIES BOX, I DO NOT GET A SUMMARY BY VENDOR, I GET THE NAME OF THE EXPRESSION ITSELF.
                          WHEN I LEFT THE FILTER ".T." IN AT FIRST, I GOT THE TOTAL FOR THE ENTIRE DATABASE, AND NOT FOR AN INDIVIDUAL VENDOR, WHICH I NEED.

                          I'M OBVIOUSLY DOING SOMETHING WRONG!! BUT WHAT ?

                          JOHN

                          Comment


                          • #14
                            Re: expression to create a database field

                            From the help file:

                            tablesum()

                            Searches the specified Lookup_Table for one or more records that satisfy the specified Filter, and returns the sum of the values contained in the Lookup_Expression.

                            The Filter must return a logical value, either True (.T.) or False (.F.). For example, to choose all of the records in a table, use the logical constant ".T." as a filter.

                            To choose only the records where the STATE field is equal to TN, use the filter "STATE = 'TN'". Note that the entire filter is always in quotations, and the character value, TN, is in single quotes.
                            Al Buchholz
                            Bookwood Systems, LTD
                            Weekly QReportBuilder Webinars Thursday 1 pm CST

                            Occam's Razor - KISS
                            Normalize till it hurts - De-normalize till it works.
                            Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
                            When we triage a problem it is much easier to read sample systems than to read a mind.

                            Comment


                            • #15
                              Re: expression to create a database field

                              VENDORS->PURCH_TOT.DBF = TABLESUM( "PURCH-PAYMENTS.DBF", "SUP_CODE","AMOUNT") = 100000.20

                              Your expression seems to be trying to set VENDORS->PURCH_TOT.DBF to some value? That doesn't make any sense to me.

                              You don't specify where you are trying to create the calculated field expression so let's assume it is on a report or form.

                              You do this by giving the calculated field a name and equating it to an expression. Since you indicated that sup_code would define the vendor and I guess that sup_code is a character field, I think you want something like

                              v_total = TABLESUM( "PURCH-PAYMENTS.DBF", "SUP_CODE = "+quote(sup_code.value),"AMOUNT")

                              This assumes that the sup_code appears on the report or form and that it has the object name sup_code. One determines the object name by examining its properties (right click on it).

                              PS The dash/minus sign in PURCH-PAYMENTS.DBF does not follow recommended naming guidelines. You may be able to get by for a while but it has been known to be the source of hard to diagnose problems later.
                              There can be only one.

                              Comment

                              Working...
                              X