Help with Basic Calculated Field

[cdibi]

I am trying to create a calculated field that is a nested if then else that results in a character field. How do i go about doing this? For example:

If a="Y" and b="N"
then war="1 Year"
else if a="T" and b="S"
then war="2 year"
else if a="R" and b="Z"
then war="3 Year"
else war="None"

I know how to make the if then else statement but don't know how to place it in the calculated field expression.

[Tim Kiebert]

Re: Help with Basic Calculated Field

When working with expressions you don't use the statement version as you would in a script. Instead you use the if() function. The expression would be

If(a="Y" .and. b="N","1 Year",if(a="T" .and. b="S","2 Year",if(a="R" .and. b="Z","3 Year","None")))

You might also check out the case() function.

If the expression gets more complicated or you want to use it in multiple locations or in combination as part of another expression you could make it a UDF (user defined function) on the code tab.

FUNCTION get_war AS C (a as C,b as C )
    
    select
        case a="Y" .and. b="N"
            Get_War = "1 Year"
        case a="T" .and. b="S"
            Get_War = "2 Year"
        case a="R" .and. b="Z"
            Get_War = "3 Year"
        case else
            Get_War = "None"
    end select    
    
END FUNCTION

Save this in a new function named Get_War and then in the calculated field you would enter Get_War(a,b) where a and b hold the values.

[johnkoh]

Re: Help with Basic Calculated Field

Hi Tim,

Save this in a new function named Get_War and then in the calculated field you would enter Get_War(a,b) where a and b hold the values.

Could you show me little be more detail what to do after save the function.

[Tim Kiebert]

Re: Help with Basic Calculated Field

I am not really sure what other details to give you. Once the function is saved to the code tab like any other function you just use it where you need to, supplying the needed parameters as defined by the function. In this case the parameters are a and b. They are character, so you would need to supply some character value such as a quoted string or a character type variable.

As far as the specifics of this function I have no idea on what it is/was being used for. It may very well have just been some sample to illustrate the question. As per the first post here the initial question was about nested ifs and this was just an alternate suggestion.

[Stan Mathews]

Re: Help with Basic Calculated Field

Tim's solution as a function is quite functional. The translation of your original question about if..else..end if into the expression usable if(logical test, true result, false result) could be....

If(a="Y" and b="N","1 Year",if(a="T" and b="S","2 year",if(a="R" and b="Z","3 Year","None")))

[johnkoh]

Re: Help with Basic Calculated Field

Good morning Tim and Stan,

I was looking for How to fire(? I am not right word but I picked from an alpha book) the Function in the Table level.

In my sample, I have a function and named Tax_state(). I want to apply it on my payroll form at Table Level.

This application is one of my problem work with the Xbasic

I have attached the zip file.

[Stan Mathews]

Re: Help with Basic Calculated Field

You call the function with the statement

tax_state()

This code needs to be placed in some event, maybe the onrowchange of the browse?

I think you need to reconsider the section

'write to table
	dim t as p
	t = table.open("payroll_days")
	t.enter_begin()
	  t.st_tax_sum = vStTaxAmt
	t.enter_end(.t.)

This creates a new record in the payroll_days table and fills in the st_tax_sum field. It does not fill in any other fields in the new record. It does not fill in the st_tax_sum field for the current record as you might have intended.

Screenshot shows the default browse of the payroll_days table after making

tax_state() the event code for the onrowchange of the browse on the certify payroll form.

[Mike Wilson]

Re: Help with Basic Calculated Field

John,
The way you have it set, you have to place focus on the record that you want CHANGED. I used a browse button event to do this in the returned example to get focus on the record to change and 'fire' the function. HOWEVER. The script is not a table.open...table_enter, it is table.get.... table_change.

[Keith Hubert]

Re: Help with Basic Calculated Field

Just as a matter of interest, why cant this be an OnSaveRecord, Event?

[johnkoh]

Re: Help with Basic Calculated Field

Mike,
Is it possible without strike xB key ?

[Mike Wilson]

Re: Help with Basic Calculated Field

John,
I am sorry.. I don't understand your question. the xB is a browse button. It is a calculated fied xb="" so there is a place to put the button.

[johnkoh]

Re: Help with Basic Calculated Field

Good morning, Mike

I mean without click on "the xB is a browse button".

[Mike Wilson]

Re: Help with Basic Calculated Field

John,
yes there are other ways. It depends on how you are entering data. Try what Keith wrote about... place tax_state() in the table field rule CanSaveRecord or OnSaveRecord record event or try the form event CanSave or OnSave. See what works best for you. Start with the record CanSaveRecord first. None the less, I would replace the write to table portion of your script to:

'write to table
topparent:browse3:st_tax_sum.value = vStTaxAmt

[johnkoh]

Re: Help with Basic Calculated Field

Mike,
Thank you.

I tried on "OnSaveRecord" Field Rule and changed the script as

'write to table
dim t as p
t = table.get("payroll_days")

t.change_begin()
't.Payroll_id = vPayrollId
t.st_tax_sum = vStTaxAmt
t.change_end(.t.)

parentform.Resynch()
topparent:browse3.refresh()

END FUNCTION

So far, works fine.

Thank you for your help again.