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

Invoicing totals

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

    #31
    Re: Invoicing totals

    Correct, thanks Tim. Been at this too long today.
    There can be only one.

    Comment


      #32
      Re: Invoicing totals

      Originally posted by barry685 View Post
      Stan,
      I was trying to not use posting, because I didn't trust it. I was hoping there was somewhere that I can create these calculations so that when I need to view,or print an invoice, or know a particular customers balance that I would not have to redefine these calculations over and over. ....
      This is a cases of pay me now or pay me later. By storing the total you may save the need to create calcs where you want to display the total but then you will need to maintain the integrity of the total value at any point where the user or the system interacts with the detail table. This may be more work than that which you are trying to avoid.


      One method that does save some time and effort with calculations is to make them globally available. You do this by defining them at the table or set level while you have the table or the set in design structure mode.

      Using alphasports as an example. Put the invoice_Header table in design structure mode. On the top toolbar you will find the familiar XY button. Press it and enter the following to create a calculated value
      Code:
      c_total = dbsum("invoice_items","Invoice_Nu",Invoice_Number,"extension")
      Click all the appropriate 'OK', 'Save', and 'Close' buttons and then put the invoice form or invoice report in edit mode.
      Check out the calculated fields section in the drag drop list. you should find the new calc value listed as c_total(Table: INVOICE_HEADER) This value will now be available where ever the invoice_header table is used.

      http://wiki.alphasoftware.com/Calculated+Values
      Tim Kiebert
      Eagle Creek Citrus
      A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

      Comment


        #33
        Re: Invoicing totals

        Originally posted by Stan Mathews View Post
        Correct, thanks Tim. Been at this too long today.
        I know the feeling.
        Tim Kiebert
        Eagle Creek Citrus
        A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

        Comment


          #34
          Re: Invoicing totals

          Originally posted by barry685 View Post
          Robert,
          Yes would like to see what you have done.
          Here is the code that I had pre-defined in a calculated field on the browse tab.
          xc_FOB = dbsum("points_tv01","Fobandyear",alltrim(Awards_Tv01->Keyfob)+chr(47)+alltrim(str(Awards_Tv01->Year)),"Amount") Tables were linked on field "KEYFOB"

          The problem that I encountered was possibly due to the browse being based on a single table.
          Because the form (where this browse sits) is based on a set made up of two tables.....
          ~Awards_Tv01 (parent table) is linked to Points_Tv01 (child table)

          When creating xc_FOB variable using dbsum() using the expression builder it looked like this:
          xc_FOB = dbsum("points_tv01","Fobandyear",alltrim(Keyfob)+chr(47)+alltrim(str(Year)),"Amount")

          Tables were linked on field "KEYFOB", the year field was also in browse.

          Ironically: This worked fine when the browse itself was viewed separate from the form.... But when placed in the form it did not return any totals! (The calculated field was not displaying any number and was blank!)


          To resolve this, I added Awards_Tv01-> in two places to explicitly point to the record fields values in the Awards_Tv01 table. ~thus explicitly defining the keyvalue in dbsum()

          Actually, upon testing this evening, I found the only place I had to add Awards_Tv01-> was in the second place where the field name was "year". Upon review, I probably shouldn't have originally used "year" as a field in the table (in the first place), but maybe there is something else going on here too!

          I still don't understand why this would only present a problem only when the browse was placed in a form.... ~If anyone cares to comment, "I'm all ears!"
          Last edited by SNusa; 01-26-2012, 02:34 AM.
          Robert T. ~ "I enjoy manipulating data... just not my data."
          It's all about the "framework." (I suppose an "a5-induced" hard drive crash is now in order?)
          RELOADED: My current posting activity here merely represents a "Momentary Lapse Of Reason."

          Comment


            #35
            Re: Invoicing totals

            Have a look around this Barry. Not exactly what you want but it might give you a lead.
            I created this as an example for another Forum user showing calcs on screen.
            Attached Files
            See our Hybrid Option here;
            https://hybridapps.example-software.com/


            Apologies to anyone I haven't managed to upset yet.
            You are held in a queue and I will get to you soon.

            Comment


              #36
              Re: Invoicing totals

              Trying this as calculated field in Inv_hdr
              c_subtotal = dbsum("inv_detail","Inv_id",str(Inv_Id,7,0),"extended")

              It's not working is the syntax correct?

              Comment


                #37
                Re: Invoicing totals

                Field in Inv_detail to be totaled is Extension
                If the field to be totaled is Extension then you can't use "Extended" in the dbsum().

                str(Inv_Id,7,0)
                Is this the way the index is defined in the index builder or is the index just inv_id?

                The purpose of the third parameter in the dbsum() expression is to pass a value that can be found in the index.

                c_subtotal = dbsum("inv_detail","Inv_id",str(Inv_Id,7,0),"extended")

                So if you create an index named Inv_id and the expression used is inv_id, you don't use str(Inv_Id,7,0).
                There can be only one.

                Comment


                  #38
                  Re: Invoicing totals

                  Field is "Extended"
                  Index name is Inv_id but is based on a numeric field.
                  One other thing. Extended is a calculated field , does that make any difference.

                  Comment


                    #39
                    Re: Invoicing totals

                    Calculated field won't make a difference. You don't need the str() function. Whatever inv_id value is Str(inv_id,7,0) will not be found in an index on inv_id.

                    Use c_subtotal = dbsum("inv_detail","Inv_id",Inv_Id,"extended")
                    There can be only one.

                    Comment


                      #40
                      Re: Invoicing totals

                      Regarding my previous post.... I've reaffirmed that problems can appear when using calculated fields (fields defined within the imbedded browse). (I don't think the bug is in the browse itself, but either in the way forms handle imbedded browses, or possibly as a result of how the expression builder implicitly specifies field values without specifying the table name.) ~ Or a combination of these two!

                      This occurs when you create a browse based on a single table, and use dbsum().
                      The bug is not apparent until you take that browse and embed it in a form based on a set.
                      This bug is realized because the expression builder (when building a calculated field) does not explicitly define a record field value using pointer. ( table->year ) ~ Instead, it just implicitly selects year.


                      Expression builder builds this:
                      xc_FOB =dbsum("points_tv01","Fobandyear",alltrim(Keyfob)+chr(47)+alltrim(Activeyear),"Amount")

                      Although the browse views fine on it's own... When it is placed within the form (based on a set) this calculated field FAILS to return a value in the imbedded browse.

                      Manually changing to this makes the browse work properly when embedded on a form:
                      xc_FOB = dbsum("points_tv01","Fobandyear",alltrim(tablename->Keyfob)+chr(47)+alltrim(str(tablename->Activeyear)),"Amount")

                      I don't know why, but in the example below, this only occurs on the second reference to this field, ie:

                      This doesn't work:
                      xc_FOB = dbsum("points_tv01","Fobandyear",alltrim(Keyfob)+chr(126)+alltrim(Activeyear),"Amount")

                      This works OK:
                      xc_FOB = dbsum("points_tv01","Fobandyear",alltrim(Keyfob)+chr(126)+alltrim(childtablename->Activeyear),"Amount")

                      This works too:
                      xc_FOB = dbsum("points_tv01","Fobandyear",alltrim(childtablename->Keyfob)+chr(126)+alltrim(childtablename->Activeyear),"Amount")


                      At first I thought the problem resulted from the record field name "year" so I changed it to "Activeyear". After extensive testing, Ive eliminated all sorts of possibilities by modifying much of my code..... It's worth noting that indexes exist for both keyfob, and for Activeyear.

                      CLUE: Oddly enough, I've noticed that this problem occurs whenever a calculated field calculation is made using a record field that is not present in the parent record. The problem is not specific to dbsum().
                      This happens whenever calculated fields are calculated using values that are exclusive to the the browse. (fields not present in both the browse and the parent table)

                      At face value: " It appears that if a form can't reference (see/enumerate) calculated fields/values inside the embedded browse, then the embedded browse is left unable reference it's own fields when performing 'calculated field calculations.' " ~ The browse works fine on it's own when viewed from the browse tab, but fails when embedded in a form.
                      Last edited by SNusa; 01-26-2012, 02:31 PM.
                      Robert T. ~ "I enjoy manipulating data... just not my data."
                      It's all about the "framework." (I suppose an "a5-induced" hard drive crash is now in order?)
                      RELOADED: My current posting activity here merely represents a "Momentary Lapse Of Reason."

                      Comment


                        #41
                        Re: Invoicing totals

                        Stan,
                        I have been trying Tim's approach. I have attached this expression: c_subtotal = dbsum("inv_detail","Inv_id","Inv_Id","extended") to the Inv_hedr table.
                        On my invoice form I place the calculated field c_subtotal. It Displays 0.

                        Comment


                          #42
                          Re: Invoicing totals

                          Barry,

                          I can no longer tell what you are doing and have.

                          If c_subtotal = dbsum("inv_detail","Inv_id","Inv_Id","extended") returns 0 then the expression is telling you that the text "inv_id" is not found in the inv_id index. The expression I gave you did not have the second instance of inv_id quoted.

                          If c_subtotal = dbsum("inv_detail","Inv_id",Inv_Id,"extended") is an invalid expression then your index on the inv_id field must use an expression that converts inv_id to character.

                          Can you attach a sample database here so we can see what the *&%$$ is going on?
                          There can be only one.

                          Comment


                            #43
                            Re: Invoicing totals

                            The syntax is correct, if you have
                            an INVOICE_DETAIL.DBF table and
                            an INV_ID index with a key that matches
                            str(Inv_Id,7,0) and the value to extract is based on
                            value of the field EXTENDED

                            but this you would probably have read in the help page on DBSUM()

                            So if its not working then the problem is not syntax - test the validity of your values.

                            Comment


                              #44
                              Re: Invoicing totals

                              Problem was quotes around second inv_id in the expression. Removing them makes the expression work.


                              c_subtotal.gif
                              There can be only one.

                              Comment


                                #45
                                Re: Invoicing totals

                                Thanks a lot,
                                I was giving up.

                                Comment

                                Working...
                                X