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

Complex conditional lookup

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

    Complex conditional lookup

    Hello -

    I hope someone can help me with a problem in creating a lookup for a client. This is a small lawn care company that I'm working with.

    I need to add a line items to an invoice. OK no problem right???

    The problem I'm having is kind of mulit-faceted.

    They have both 'hard items' like sprinkler parts, mower parts, etc. and they have services that, depending on what the service is, is charged differently.

    The parts I have no problem with - real similar to AlphaSports setup. Where I'm running in to problems is looking up for the services.

    I have several spreadsheets they use (of course no rhyme or reason in the pricing - I'm trying to get them to change still) that I converted to tables. Most of the services are charged out based on square footage of the property they are treating (Fertilizer, insect controll, weed controll, etc. Some are also charged hourly.

    The customer table contains a field for lawn area (in sq ft) and account type. Depending on the service I need to find the area measurement in the appropriate range in the servicelookup table and put that cost in the line item based on commercial or residential.

    The other problem I'm having is how to put both 'parts' and 'services' on the same invoice. In other words how to choose where I'm looking for the item number to charge out.

    I've attached a small test db that I've been playing with. May be some one can see something I'm missing.

    Thanks for your help!

    Scott

    #2
    Re: Complex conditional lookup

    Hi Scott,

    I have not looked at your app but the first thing that comes to mind is a second child table for the items that are charged by the sq ft. You will need to decide if the labour charge fields could be included in one of these tables or have another child table.

    The lookup would be filtered based on commercial or residential. This would be a filed value in the customer table.

    The the child tables would be used as sub reports on Invoices etc.
    Last edited by Keith Hubert; 04-24-2009, 05:57 PM.
    Regards
    Keith Hubert
    Alpha Guild Member
    London.
    KHDB Management Systems
    Skype = keith.hubert


    For your day-to-day Needs, you Need an Alpha Database!

    Comment


      #3
      Re: Complex conditional lookup

      Keith -

      Thanks for the quick reply.

      I do have a separate table for the lookup of the service. Also a table for the products. The problem I'm having is I can look up the products just fine and they fill in the lineitems table just fine - I just can't get the switch over to the service table to work.


      Also the services has a range of lawn area. so... (not real figures I'm not in front of it right now.)

      AREA Residential Commercial
      0 - 1999 sq ft $33.95 $31.95
      2000 - 2499 sq ft $38.95 $36.95
      2500 - 2999 sq ft $43.95 $41.95
      and so on.

      Say the customer I'm entering in the invoice for has a lawn with 2105 sq ft (a field in the customer table) and is residential (also a field in the customer). I would need to find it in the table in the 2000-2499 range for area and determine that the price to fill in would be $38.95

      It is the switching between the two lookups and the determining where the customers area falls in the service ranges that have me baffled.

      Thanks.

      Scott

      Comment


        #4
        Re: Complex conditional lookup

        Hi Scott,

        It is getting late here so I'll just answer the question about the second lookup.

        Assuming there is more than 1 item, then you would have a second browse on the form.

        With regard to the area costings, that would be a calculated field using a Case Select function. See the help file.

        I'll have another look at this interesting project in the morning.
        Regards
        Keith Hubert
        Alpha Guild Member
        London.
        KHDB Management Systems
        Skype = keith.hubert


        For your day-to-day Needs, you Need an Alpha Database!

        Comment


          #5
          Re: Complex conditional lookup

          Scott, I'm having a look but have a question... in your Product table the Product_Id is defaulting to a format of Pnnn. I understand that it's autoincrement, but where are you setting the "P"?

          Comment


            #6
            Re: Complex conditional lookup

            David -

            The "P" was set in the first record. P0001 Alpha continues the numbering convention automatically in the auto increment rules. Kind of silly but it is a left over from old A4 DOS days for me. Helps me to know what numbers and ids are what when I'm looking at the tables.

            Comment


              #7
              Re: Complex conditional lookup

              Keith -

              I thought about the second browse a bit. I suppose I could put a second browse to the same lineitems (invoice_items) if I had to. I just think that would be bit confusing adding invoice items to the invoice for my client. Not to mention the screen real estate it would take up.

              I thought a bit about placing a button on the form to 'add' a new item to the invoice. Then using a dialog box to set a variable as to which lookup to perform. There is 4 different price sheets they use that all have different rates. I'm still trying to normalize the data to get it down to a tight db. They are just reluctant to change because of the "it's the way we've always done it" and if it "ain't broke why fix it?" mentality.

              Scott

              Comment


                #8
                Re: Complex conditional lookup

                Scott... not sure if this is what you want...

                Is the idea to build a list of Products/Services for the client in the Invoice form?

                If so, in the Product table include services as well. For those Services based on Sq. Ft. enter a cost of 0.00

                Now, when you pick a Product, the browse will be filled in, as it is now.
                But, when you pick a Service, and it's 0.00 then the servicelookup table will be accessed, the sq. ft. found in area and the Cost will be placed in the price field. I'm not very good with complex expressions yet - I tend to go right after the source using code. The code for this is in the field rules for the set under Product_Id.

                I also added a condition to the Product_Id lookup where it sets price so that the cost of 0.00 is not written to the price field.

                Also, I took off the .T. on the Skip field for Product_Id - not sure why now - maybe it was so I could enter services as S001.

                This also lets you put Parts and Services on the same invoice.

                Attached is an updated database.

                Comment


                  #9
                  Re: Complex conditional lookup

                  Scott, when you get into the Event code for Product_Id in the table, add the following lines after the Table_Open

                  query.sort = "area"
                  indx = tbl.query_create()

                  This will make sure the Area is sorted so that the test for Area will be correct.

                  Comment


                    #10
                    Re: Complex conditional lookup

                    Hi David,

                    That Lookup is rather neet, but how would you filter the lookup of Product->Price based on the value of Accttype?
                    Regards
                    Keith Hubert
                    Alpha Guild Member
                    London.
                    KHDB Management Systems
                    Skype = keith.hubert


                    For your day-to-day Needs, you Need an Alpha Database!

                    Comment


                      #11
                      Re: Complex conditional lookup

                      You could add a filter to the code. The ServiceLookup table doesn't have AcctType right now, but it makes sense to add it if the costs are different for services based on area. It's not great that you have to loop through the ServiceLookup table, but the # of records are finite and few so...

                      Comment


                        #12
                        Re: Complex conditional lookup

                        Hi David,

                        I think you missed my filter question, I was asking about doing a filter to the Product table if the customer is either Domestic or Retail.

                        Ideally Conditional Lookup tables can be used, so that different price fields can be displayed.
                        Regards
                        Keith Hubert
                        Alpha Guild Member
                        London.
                        KHDB Management Systems
                        Skype = keith.hubert


                        For your day-to-day Needs, you Need an Alpha Database!

                        Comment


                          #13
                          Re: Complex conditional lookup

                          Hi David and Keith -

                          I just got in this morning and will look this over. Thanks in advance for all you are helping me with this.

                          It always amazes me the willingness of the Alpha community to help each other out.

                          More soon - Scott

                          Comment


                            #14
                            Re: Complex conditional lookup

                            I see what you mean Keith. I've played with it but can't see a way using Lookups to set either the Commerical or RetailPrice based on AcctType AND be able to set Service price. I'd use code at this point.

                            Comment


                              #15
                              Re: Complex conditional lookup

                              David -

                              I guess I need you to step me through what you did.

                              When I go into to the Invoice form to create a new invoice it now behaves differently than what I think it should.

                              Instead of starting at the top with choosing a customer it starts out entering a new invoice item for the current invoice.

                              On entering a product I still only get the commercial pricing. Not the retail.

                              I don't see any fill in if I enter a service 'part'.

                              I'll keep working with this to try and see what you did.

                              Thanks!

                              Comment

                              Working...
                              X