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

Desktop Calendar caculation function?

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

    #16
    Re: Desktop Calendar caculation function?

    Hi Paul,
    I reworked the function to use "tenant agreements" as the table.
    Lets see what it does.


    Code:
    ?addyears(date(),5)
    = {07/27/2023}
    
    ?*add_years(date(),5)
    = {07/27/2023}
    Code:
    FUNCTION autoIncrease AS C (qDate AS D )
    	
    dim tbl as p
    dim tracker as n =1
    dim tname as c
    tname="tenant agreements"
    
    tbl = table.open(tname,FILE_RW_SHARED)
    
    tbl.fetch_first()
    
    while .not. tbl.eof() .or. tracker <= tablecount(tname,".t.")
    if qDate >= addyears(tbl.escalator_date,1) then
    tbl.change_begin()
    tbl.current_rent = tbl.current_rent + (tbl.current_rent*tbl.escalator_rate)
    tbl.escalator_date=addyears(tbl.escalator_date,1)
    tbl.change_end(.t.)
    end if
    tracker=tracker+1
    tbl.fetch_next()
    end while
    	
    	autoIncrease = "Finished"
    END FUNCTION
    Gregg
    https://paiza.io is a great site to test and share sql code

    Comment


      #17
      Re: Desktop Calendar caculation function?

      Try it as:

      Code:
      FUNCTION autoIncrease AS C (qDate AS D )
              autoIncrease = "" 'default return value
      	dim tbl as p
      	tbl = table.open("tenant agreements",FILE_RW_SHARED)
      	tbl.fetch_first()
      	tbl.batch_begin()
      	while .not. tbl.fetch_eof()
      		if qDate >= addyears(tbl.agreement_escalatordate,1) then
      			tbl.change_begin()
      			tbl.agreement_currentrate = tbl.agreement_currentrate + (tbl.agreement_currentrate*tbl.agreement_escalatorpercentage)
      			tbl.agreement_escalatordate=addyears(tbl.agreement_escalatordate,1)
      			tbl.change_end(.t.)
      		end if
      		tbl.fetch_next()
      	end while
      	tbl.batch_end()
      	tbl.close()
      	autoIncrease = "Finished"
      END FUNCTION
      To use the function you need to call it as you would any other function. Something like

      Code:
      if autoIncrease(date()) = "Finished"
      	msgbox("Auto increase completed successfully.")
      else
      	msgbox("Auto increase did not complete.")
      end if
      end	
      
      
      FUNCTION autoIncrease AS C (qDate AS D )
      	autoIncrease = "" 'default return value
      	dim tbl as p
      	tbl = table.open("tenant agreements",FILE_RW_SHARED)
      	tbl.fetch_first()
      	tbl.batch_begin()
      	while .not. tbl.fetch_eof()
      		if qDate >= addyears(tbl.agreement_escalatordate,1) then
      			tbl.change_begin()
      			tbl.agreement_currentrate = tbl.agreement_currentrate + (tbl.agreement_currentrate*tbl.agreement_escalatorpercentage)
      			tbl.agreement_escalatordate=addyears(tbl.agreement_escalatordate,1)
      			tbl.change_end(.t.)
      		end if
      		tbl.fetch_next()
      	end while
      	tbl.batch_end()
      	tbl.close()
      	autoIncrease = "Finished"
      END FUNCTION
      There can be only one.

      Comment


        #18
        Re: Desktop Calendar caculation function?

        Thank you Stan!

        It was actually calling the function from the button but I needed below to be able to see it in the form I was calling from. It is a Modal form.

        Now I will try what you put above.
        I am currently getting the wrong calculation as it comes up as 400 new rate and it changes escalator date to the next year, so it will not increase each run, so that is good, just need to see why $400 from $100 calculation.

        topparent.Commit()
        topparent.Resynch()
        topparent.Refresh_Layout()

        Thank you all in all the help you give as I really appreciate it as I don't know what I am always doing.

        Comment


          #19
          Re: Desktop Calendar caculation function?

          Is our percentage stored as 3 or .03?
          There can be only one.

          Comment


            #20
            Re: Desktop Calendar caculation function?

            The function as I wrote it assumes .03.
            Gregg
            https://paiza.io is a great site to test and share sql code

            Comment


              #21
              Re: Desktop Calendar caculation function?

              Hi all,
              It was my fault as when you enter the escalator the form shows 3% and it writes 3 to the table and that was the issue.
              I changed calculation to convert the entered percentage of 3 to .03 for calculation
              (tbl.agreement_currentrate*(tbl.agreement_escalatorpercentage/100))

              Thank you all I know I might have another question on this as to additional code to calculate countdown years I believe, but I need to look at this and see if I can figure it out on how to add a little more.

              Thank you all, this works out great!
              Paul
              Last edited by datacaster; 07-27-2018, 11:10 AM.

              Comment


                #22
                Re: Desktop Calendar caculation function?

                Hi Paul,
                It was my fault as a lazy programmer (I took the shortcut of assuming an number greater than zero and less than one.
                The rewrite of the function shown below allows for either option, but does not check for a zero value.

                For your convenience, I am showing the results of some basic testing (this works because I have only one record in the table).
                Code:
                dim tablename as c="tenant agreements"
                ?"Escalator Date : "+lookup(tablename,".t.","dtoc(escalator_date)")+"  Current Rent : "+lookup(tablename,".t.","alltrim(str(current_rent,7,2))")
                = "Escalator Date : 07/01/2017  Current Rent : 250.00"
                ?autoIncrease(date())
                = "Finished"
                ?"Escalator Date : "+lookup(tablename,".t.","dtoc(escalator_date)")+"  Current Rent : "+lookup(tablename,".t.","alltrim(str(current_rent,7,2))")
                = "Escalator Date : 07/01/2018  Current Rent : 257.50"
                ?autoIncrease(addyears(date(),1))
                = "Finished"
                ?"Escalator Date : "+lookup(tablename,".t.","dtoc(escalator_date)")+"  Current Rent : "+lookup(tablename,".t.","alltrim(str(current_rent,7,2))")
                = "Escalator Date : 07/01/2019  Current Rent : 265.23"
                Code:
                FUNCTION autoIncrease AS C (qDate AS D )
                	
                dim tbl as p
                dim tracker as n =1
                dim tname as c
                tname="tenant agreements"
                
                '''debug(1)
                tbl = table.open(tname,FILE_RW_SHARED)
                
                tbl.fetch_first()
                
                while .not. tbl.eof() .or. tracker <= tablecount(tname,".t.")
                if qDate >= addyears(tbl.escalator_date,1) then
                tbl.change_begin()
                if tbl.ESCALATOR_RATE < 1 then
                	tbl.current_rent = tbl.current_rent + (tbl.current_rent*tbl.escalator_rate)
                else
                	tbl.current_rent = tbl.current_rent + (tbl.current_rent*(tbl.escalator_rate/100))	
                end if
                
                tbl.escalator_date=addyears(tbl.escalator_date,1)
                tbl.change_end(.t.)
                end if
                tracker=tracker+1
                tbl.fetch_next()
                end while
                	
                	autoIncrease = "Finished"
                END FUNCTION
                Gregg
                https://paiza.io is a great site to test and share sql code

                Comment


                  #23
                  Re: Desktop Calendar caculation function?

                  Originally posted by madtowng View Post
                  I reworked the function to use "tenant agreements" as the table.
                  Alpha allows you to include spaces in table names almost to the point of writing sentences. That doesn't mean you should. If for no other reason than keeping expressions short many developers use the shorter field names.

                  https://www.alphasoftware.com/docume...erminology.xml

                  Specifically...

                  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 spaces.

                  That said, the AlphaSports sample database has tables

                  invoice menu
                  product sales
                  product sales summary.
                  There can be only one.

                  Comment


                    #24
                    Re: Desktop Calendar caculation function?

                    Hello All,
                    Thank you all for your excellent help.

                    I added a piece to the code Greg provided and Stan's info and it works, but I have to ask if I added a months field could it also have months countdown also.

                    I have on form a field showing number of renewals meaning how many times can this lease renew
                    Also a field of renewal length years which is how many years each renewal term is for the lease
                    So 5 renewal terms with 1 year length equals 5 years lease or 5 renewal terms with renewal length of 5 years equals 25 year lease

                    The code you provided allowed me to count down the lease each year, but I am not clear on how to add a month countdown so it would go from 1 year on renewal date which is the escalator date usually and the next month the year goes to zero and month goes from zero to 11 and the month after 10 and so on.

                    Months are not constant values since 30 days and 31 days and 28 days (29 days leap year), so I don't think you can subtract, but I know nothing on this.

                    The code I added to your help is

                    tbl.change_begin()
                    tbl.agreement_currentrate = tbl.agreement_currentrate + (tbl.agreement_currentrate*(tbl.agreement_escalatorpercentage/100))
                    tbl.agreement_escalatordate=addyears(tbl.agreement_escalatordate,1)
                    'Subtract 1 year from yearsleft field
                    tbl.agreement_YearsLeft=tbl.agreement_YearsLeft - 1
                    tbl.change_end(.t.)
                    end if
                    'Check years left and if 0 reset years left to renewal Term length number field and reduce renewals left by 1
                    if tbl.agreement_YearsLeft = 0 then
                    tbl.change_begin()
                    tbl.agreement_YearsLeft = tbl.Agreement_Renewaltermlength
                    tbl.agreement_RenewalsLeft = tbl.agreement_RenewalsLeft - 1
                    tbl.change_end(.t.)
                    end if

                    So yes I am lost again or tired or both.

                    Thank you again for the stuff you provided. This will allow me to run report of expiring leases or to maybe flag tenant or something like that.
                    Also I need service reminders for things like HVAC maintenance and that is usually 6 months, maybe I can use .5 as to 1 for what you provided, didn't test yet, but months would be the other possibility here too maybe.

                    Thank you all again!
                    Paul

                    Comment


                      #25
                      Re: Desktop Calendar caculation function?

                      Paul,
                      This is usually much easier to do if you provide the table structure with some sample data.
                      It would allow us to work with the same data you plan to work with rather than us guessing.
                      It seems what you're trying to accomplish is relatively simple. I work with a lot of things that
                      expire and require advance notice of the expiration.
                      Gregg
                      https://paiza.io is a great site to test and share sql code

                      Comment


                        #26
                        Re: Desktop Calendar caculation function?

                        Originally posted by madtowng View Post
                        Paul,
                        This is usually much easier to do if you provide the table structure with some sample data.
                        It would allow us to work with the same data you plan to work with rather than us guessing.
                        It seems what you're trying to accomplish is relatively simple. I work with a lot of things that
                        expire and require advance notice of the expiration.
                        Hi Greg,
                        I hope I did this right. So, I zipped up the table and uploaded here. Never did this before, so I hope it is here.
                        As I said the countdown is my thought to see if you look at a tenants page and I was looking to potentially have some sort of message pop up on some page possibly just warning x months ahead that these agreements are expiring.

                        Thank you for looking it over!
                        Paul
                        Attached Files

                        Comment


                          #27
                          Re: Desktop Calendar caculation function?

                          Hi Paul,

                          I need to get to my notebook fired up to test this.
                          In several of my current projects, I have the system send out emails
                          regarding expiring certifications/training XX days before the expirations,
                          as well as put them onto a web page. Am I on the right track for what you want done ?
                          Gregg
                          https://paiza.io is a great site to test and share sql code

                          Comment


                            #28
                            Re: Desktop Calendar caculation function?

                            That sounds great other than this is a desktop application only. I plan to add things at the sites that need maintenance scheduled like heating and air conditioning systems, generator, etc. and hope to move what is done here there for notices of needing to schedule maintenance items.
                            I just figured run reports or have a button that parses table I guess for records that are <1 month or <2 months to say I need to schedule, but never put much thought as I just started playing with this.

                            Thank you Greg!

                            Paul

                            Comment


                              #29
                              Re: Desktop Calendar caculation function?

                              Hi Paul,

                              Experiment with this function, see if it basically does what you want it to do.
                              it will require a date the should be within 3 months in the future to get the warning I think you want.

                              Code:
                              FUNCTION expWarning AS C (expDate AS D )
                              	'This will display a warning showing x days until expiration if today is within 3 months of the agreement expiring
                              	dim dbecount as n
                              	if date() >= addmonths(expDate,-3) then
                              		dbecount = expDate-date()
                              		ui_msg_box("Warning","This agreement will expire in "+alltrim(str(dbecount))+" days.")
                              		end if
                              END FUNCTION
                              Gregg
                              https://paiza.io is a great site to test and share sql code

                              Comment


                                #30
                                Re: Desktop Calendar caculation function?

                                My turn to hope the attachment works.
                                I notice the function/script to adjust the escalatorDate is missing so I adjusted data as necessary
                                to test functionality.

                                Let me know what you think.

                                tenant agreementsAltered2.zip
                                Gregg
                                https://paiza.io is a great site to test and share sql code

                                Comment

                                Working...
                                X