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

Calculated Field Type using previous value based on Order #

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

  • Calculated Field Type using previous value based on Order #

    This should be common but I can't figure out how to do this correctly:
    I have an Complete Order Set - Key Order Number (is linking key between both tables)
    With an Order table that has multiple Order Numbers to multiple order lines
    With an Order Lines table (one to many)

    I want the line id to increment based in relation to the qty of lines in the order.

    For example:
    Order No: 25569
    Will have 5 lines to it
    I want the first line to have the line id: 001
    2nd line id: 002
    3rd line id: 003
    4th line id: 004
    5th line id: 005

    If I then go onto Order No: 359846
    The 1st line id should be 001
    2nd line id 002, etc.

    and if I go back to order No: 25569 to add additional lines it will look to see what the last line number on that order was and go to the next line no: 006 and then if there is another line no 007, etc.


    Where and how should I go about getting this to happen.
    I was doing it in the table rules for line id: but I don't know if I should do Field Type as Calculated and then what calculation should I use? I tried last() but it says I can't use that function.
    I've tried in Data Entry and default mode using this : PREVIOUS("line","OrderNo")+001

    Bbut when I go to the next order it continues the Line id sequence it does not rely on the how many lines are per order.
    It ends up looking like this:

    Order No: 25569
    1st line id: 001
    2nd line id: 002
    3rd line id: 003
    4th line id: 004
    5th line id: 005

    If I then go onto Order No: 359846
    1st line id: 006
    2nd line id: 007

    and if I go back to order No: 25569 to add additional lines it will look to see what the last line number on that order was and go to the next line no: 008 and then if there is another line no 009, etc.


    I want it to look like this:
    Order No: 25569
    1st line id: 001
    2nd line id: 002
    3rd line id: 003
    4th line id: 004
    5th line id: 005
    6th line id: 006
    7th line id: 007

    If I then go onto Order No: 359846
    1st line id: 001
    2nd line id: 002


    Any help would be appreciated.

    Jennifer Payne

  • #2
    I'm not sure if you can do this in field rules, but we code this as follows:

    Code:
    'Get current line count for this invoice
    dim my_line_no as N
    query_filter = "invoice_no = " + my_invoice_no
    line_cnt = a5_get_records_in_query("invoice_items",query_filter)
    my_line_no = line_cnt+1
    You'd need to manipulate the result to add leading 0s if required.

    Comment


    • #3
      Storing line numbers in the one to many child table is problematic. Recommend thinking about their purpose. What's the goal? Are you numbering them because they must be in a particular order, or simply to display the order in which they were entered? What if the user wants to delete a row after entering later rows? If you don't need them in the table, don't use them. If you don't have them in the table I think there's a way to number lines in the details band of your later report, anyway.

      On the other hand if you're entering lines using a browse, and if the sequence of the lines is important, you can store a user entered line number in each row. Forget automatic numbering. Display the "order" of the rows in your browse using the line number field. This lets the user move rows around by renumbering them to suit.

      Comment


      • #4
        Originally posted by Tom Cone Jr View Post
        Storing line numbers in the one to many child table is problematic. Recommend thinking about their purpose. What's the goal? Are you numbering them because they must be in a particular order, or simply to display the order in which they were entered? What if the user wants to delete a row after entering later rows? If you don't need them in the table, don't use them. If you don't have them in the table I think there's a way to number lines in the details band of your later report, anyway.

        On the other hand if you're entering lines using a browse, and if the sequence of the lines is important, you can store a user entered line number in each row. Forget automatic numbering. Display the "order" of the rows in your browse using the line number field. This lets the user move rows around by renumbering them to suit.
        It would be for our installers so when they said they did task number... we can see what task that is.
        Or think of Purchase orders with lines.
        001 Install bathroom sink
        002 Move Fan in bathroom
        003 Tile the Bathroom Shower

        Comment


        • #5
          I believe you are trying to establish a work sequence order for delivery to installers. However you are trying to establish a work sequences order by entry order. I have a business that has a dynamic installation process and there is no possible way that record entry order is the outcome of work sequence order. When the bathroom counter is decided to be replaced after you have entered 001 Install bathroom sink, what exactly happens in your system, because the counter has to be set before the sink can be set. No chance what you are proposing would work for me. That is why I have a field specifically to order the work events and upon every entry of a new work event, all the work events are sequenced. Simple stuff using xdialogs and direct table entries. I can rustle up an example if you are seriously interested.
          Mike W
          __________________________
          "I rebel in at least small things to express to the world that I have not completely surrendered"

          Comment


          • #6
            If this is for standard installations, why not have a standards table?

            Then you enter an order, pick a standard, and generate all of the lines for that order.

            If that makes sense then we can talk about how to do it.

            ie standards table, pick a standard, append operation to add to order lines by standards key
            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


            • #7
              Maybe, Al.
              However, the client may decide to have a different order of work.
              What I don't get is the need for a defined order numerically.
              A job is a job with consumables and labour, so if "tile shower" is a job, then I am presuming there would be N sq mtrs tiles, N tubs grout, n hours labour + the unforseen factor.
              Would the client or installers care about the job number?

              Client Ref/Invoice# + Job Activity would make more sense to me.

              Jpayne, got more info?

              Btw. I have an on line estimating tool showing profit and loss on jobs if you are interested.
              Ted Giles
              Example Consulting - UK
              .

              sigpichttp://ec12.example-software.com//
              See our site for Alpha Support, Conversion and Upgrade.

              Comment


              • #8
                Originally posted by Ted Giles View Post
                Maybe, Al.
                However, the client may decide to have a different order of work.
                What I don't get is the need for a defined order numerically.
                A job is a job with consumables and labour, so if "tile shower" is a job, then I am presuming there would be N sq mtrs tiles, N tubs grout, n hours labour + the unforseen factor.
                Would the client or installers care about the job number?

                Client Ref/Invoice# + Job Activity would make more sense to me.

                Jpayne, got more info?

                Btw. I have an on line estimating tool showing profit and loss on jobs if you are interested.
                Yes. By appending the records into the line table, then line values can be changed for that job.
                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


                • #9
                  The Client Ref/Invoice# + Job Activity may be closer to what Im going for. I'm unfortunately not explaining it clearly enough.
                  It's a work order of sorts. And they installer may reference a task number if say: Task No. 1. Install bathroom Sink, they did this task but ran into a problem or addition to this work - for instance they need to let our Project Manager know that when they installed they completed task 1. They chipped the tile and now they need to order the same tile as in the task 3. (Tile from bathroom shower is same tile used as back splash on the sink).

                  There may need to be a reference and referencing task numbers:
                  1. Task complete w/damage, order more tile - same tile as task 3.
                  Vs.
                  1. Task complete w/damage, order same time that is used in bathroom shower.

                  And as you can tell one day at house 1 (purchase order 25569) can have 3 things and then on another day return for the fixing of the tile on Task 1 - that would be a NEW TASK and require a new task number for that PO which would then be Task/Line 4.

                  But what I run into is currently because I've entered tasks for 3 other purchase orders with varying amount of tasks to be done on each. Instead of PO 25569 have a 4th task to fix the tile. I get a task number of 28. The installers and PM will look at that and think where are the other 25 other tasks. (So maybe this is more for the PM to track tasks requested and tasks completed per work order/PO)

                  Here is a screenshot:
                  FYi, there are 2 databases here the (form is one), the browse is the second - linked by the 20-A307 or JOB NUMBER)
                  And the 2 Vanity tops is a basic description. Most of those basic descriptions would be Bathroom 256 and Bathroom 300
                  And then the tasks would be the specifics - one of which would be from this basic description -
                  1. Install 2 vanity tops
                  (Data entry got lazy and just put the task in)JobEntry.JPG

                  Comment


                  • #10
                    Snagging.zip

                    In the UK we have a name for fixing stuff after the installation.
                    I'ts called "Snagging".

                    Have a look at the attached for ideas.
                    Ted Giles
                    Example Consulting - UK
                    .

                    sigpichttp://ec12.example-software.com//
                    See our site for Alpha Support, Conversion and Upgrade.

                    Comment


                    • #11
                      Ted,
                      Thanks for the culture lesson and the example. I think you are spot on. The broken tile from the sink install is a child of the sink install. And the sink install can't be completed when there is a need to order tile from task 3 and fix the snag
                      Mike W
                      __________________________
                      "I rebel in at least small things to express to the world that I have not completely surrendered"

                      Comment


                      • #12
                        Originally posted by Mike Wilson View Post
                        Ted,
                        Thanks for the culture lesson and the example. I think you are spot on. The broken tile from the sink install is a child of the sink install. And the sink install can't be completed when there is a need to order tile from task 3 and fix the snag
                        Mike, let me know if you get this as a Notification Email please.
                        I'm trying to work out why I still dont get notifications!
                        Ted Giles
                        Example Consulting - UK
                        .

                        sigpichttp://ec12.example-software.com//
                        See our site for Alpha Support, Conversion and Upgrade.

                        Comment


                        • #13
                          Hi Ted,
                          I received this as a notification
                          Mike W
                          __________________________
                          "I rebel in at least small things to express to the world that I have not completely surrendered"

                          Comment


                          • #14
                            Originally posted by Mike Wilson View Post
                            Hi Ted,
                            I received this as a notification
                            When you quote someone, then a Notification is sent. If you don't, then there doesn't seem to be one.
                            I'll ask Lenny - if he is still talking to me that is!
                            Ted Giles
                            Example Consulting - UK
                            .

                            sigpichttp://ec12.example-software.com//
                            See our site for Alpha Support, Conversion and Upgrade.

                            Comment

                            Working...
                            X