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
Devcon 2020 Goes Virtual! LEARN MORE >

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 derived from another calculated field

Collapse
X
Collapse
 
  • Page of 2
  • Filter
  • Time
  • Show
Clear All
new posts
Previous 1 2 template Next
    #16
    Re: Calculated field derived from another calculated field

    simple rules are for normalizarion:
    normalize to the max and the denormalize until it works.
    no one wants the same names to be everywhere in every table, but sometimes it is necessary.
    Dave Mason
    [email protected]
    Skype is dave.mason46

    Comment

      #17
      Re: Calculated field derived from another calculated field

      Mike,

      This code will not work with your table at all but just want to show you an idea the way to avoid ... problem.

      I have this kind of Cal_fields and I do not want to use more than twice of the lookup() fuction

      xGetSomeValue = lookup("table","key_id="+quote(lookup("table_2","Id="+quote(aField),"key_id"),"Some_field")

      Code:
      feepmtdd = If(calc->INSTFEE=0.OR.calc->INSTFEE=20,calc->INSTFEE, 
               If(AREA_CODE>16,calc->INSTFEE,
                  If(DIRDEB->FREQ='Y'.OR.DIRDEB->FREQ='M',calc->INSTFEE,calc->INSTFEE+5)))


instfee =  If(member->WAIVED>{1/1/1901},FEES->INSTWAIVED, 
              If(member->STU_TILL>date_value(feeyear,1,1).AND.member->INST_GRADE='AIAgrE',FEES->INSTPRE76, 
                If(member->RETD>{1/1/1901}.AND.RETD<{1/1/1976},FEES->INSTPRE76, 
                    If(member->RETD>{1/1/1901},FEES->INSTRETD, 
                       If(member->HARDSHIP>{1/1/1901},FEES->INSTHDSHIP,FEES->INSTFULL)))))


'*Sample for your Cal Instfee

FUNCTION Get_Instfee AS N (vPrimKey AS C, vGivenDate as d )
	'Open a table and find the records
	tbl = table.open("member")
	query.filter = "Memno = "+quote(vPrimKey)  'Changed to "C" type and set Auto_incr
	query.order =""
	qry = tbl.query_create()
		'Get the table records
		vWaived = tbl.Waived
		vStutill = tbl.STU_TILL
		vInstGrade = tbl.Inst_grade
		vRetd = tbl.Retd
		vHardship = tbl.Hardship
		
		'Does one member will get those fields data?	

		'Comparison and set the variable to searching from Fees table
		vKeyId =""
		If vWaived > vGivenDate then
		    'vKeyId = tbl.key_id  '????   'Need the Id for search from Fees table.
			Get_Instfee = FEES->INSTWAIVED 'want to say it but it will not work here...
			
			if  vStutill> vGivenDate .and. vInstGrade = "Aiagre" then
			    Get_Instfee = FEES->INSTPRE76  'want to get it but it will not work here...
			end if  
			
			'and ... going on...
			
		end if
		
			'as you may noticed it, It will be too complicate to write a code here 
		
	qry.drop()		
	tbl.close()
	
			
	'Open and get the fee from Fees table
	tFee = table.open("fees")
	query.filter = "Fee_id ="+quote(vKeyId)  'fake
	query.order = ""
	qry2 = tFee.query_create()
		Get_Instfee = tFee.Instwaived
	qry2.drop()
	tfee.close()
	
END FUNCTION

FUNCTION Get_feePmtdd AS C (vSomeId AS C ,vPrimKey AS C, vGivenDate as d )
	'When does it should be run? It can not run it self as "calc->INSTFEE=0.OR.calc->INSTFEE=20,calc->INSTFEE, "
	
	vResult = Get_Instfee(vPrimKey, vGivendate) 'after ran this code
	
	select
	    case vResult <= 20
	    	'do something here
	    	'Get_feePmtdd = 'calc->INSTFEE  'again? How? 
	    case vResult ???
	    	'do something here
	    case else
	    	'do that here		
	end select

	'(1) If(calc->INSTFEE=0.OR.calc->INSTFEE=20 
    '(2) If(AREA_CODE>16,calc->INSTFEE,
    '(3) If(DIRDEB->FREQ='Y'.OR.DIRDEB->FREQ='M'
	
	'The 1, 2,& 3 are not logical(?) condition to compare each oter so write similar code again
	
END FUNCTION

Now, you can use the Cal_value  as  FeePmtdd = Get_feePmtdd(field_name, MemNo, Given_date)

      Comment

        #18
        Re: Calculated field derived from another calculated field

        Speaking of normalization, it is probably best not to use a table or field name that Alpha will have to modify to use internally. You can check your choice in the interactive.

        ? table.name_normalize("long name with (&) punctuation")
        = "long_name_with_punctuation"

        ? table.name_normalize("short name with space")
        = "short_name_with_space"

        Speaking of building calculated fields, the longer the table and field names the fewer table and field names you can use in an expression (1024 characters max).

        Code:
        ? LEN("If(.NOT.Like('A*',INST_GRADE),INST_GRADE,If((FEEYEAR-DBT_END<=2.OR.FEEYEAR-Year(CERTSTART)<=2).AND.INST_GRADE='AIAgrE','Assoc1',If((FEEYEAR-DBT_END<=2.OR.FEEYEAR-Year(CERTSTART)<=2).AND.INST_GRADE='AMIAgrE','AssMem1',If((FEEYEAR-DBT_END>2.OR.FEEYEAR-Year(CERTSTART)>2).AND.(FEEYEAR-DBT_END<=4.OR.FEEYEAR-Year(CERTSTART)<=4).AND.INST_GRADE='AIAgrE','Assoc2',If((FEEYEAR-DBT_END>2.OR.FEEYEAR-Year(CERTSTART)>2).AND.(FEEYEAR-DBT_END<=4.OR.FEEYEAR-Year(CERTSTART)<=4).AND.INST_GRADE='AMIAgrE','AssMem2',If(INST_GRADE='AIAgrE','Assoc3','AssMem3'))))))")
= 546

? len("If(.NOT.Like('A*',IGRD),IGRD,If((FYR-DTND<=2.OR.FYR-Year(CSTT)<=2).AND.IGRD='AIAgrE','Assoc1',If((FYR-DND<=2.OR.FYR-Year(CSTT)<=2).AND.IGRD='AMIAgrE','AssMem1',If((FYR-DTND>2.OR.FYR-Year(CSTT)>2).AND.(FYR-DTND<=4.OR.FYR-Year(CSTT)<=4).AND.IGRD='AIAgrE','Assoc2',If((FYR-DTND>2.OR.FYR-Year(CSTT)>2).AND.(FYR-DTND<=4.OR.FYR-Year(CSTT)<=4).AND.IGRD='AMIAgrE','AssMem2',If(IGRD='AIAgrE','Assoc3','AssMem3'))))))")
= 407

        Imagine if you were writing an expression referring to fields in the mapped table for export & reports table.

        mapped table for export & reports->textacctbalance

        50 characters in one reference alone.

        Note: The method is called .name_normalize(), don't know why. It will accept most any string as a parameter and "normalize" it. Nothing directly to do with a table that I can see. Handy tool.
        Last edited by Stan Mathews; 06-13-2013, 03:41 PM.
        There can be only one.

        Comment

          #19
          Re: Calculated field derived from another calculated field

          Stan,

          That is another good advise.

          Comment

            #20
            Re: Calculated field derived from another calculated field

            Thanks everyone for your guidance. I've tidied up the field and table names and had a good go at normalisation. It's been quite a lesson.
            I think the original problem arose through my using calculated values rather than calculated fields. The error messages disappeared once I stopped using calculated values and replaced them with calculated fields.

            Mike

            Comment

            Previous 1 2 template Next
            Working...
            X