The "if" Factor

[Melv]

Help.....

I have established a database and now require a calc that I cant seem to sort..

 

I have sixteen fields "period1,period2,period3, etc....,period16" and have a standard value that is specific to the record that needs to be inserted into all of the fields... Thats easy..

 

But. The problem I am having is........ I want the script to look at another field called "period", whatever number that is in that field is where i want the insertion to start.

 

example.

The Value to be inserted is £312.00.

"period" has the number "4" in.

the script should do.... look at "period" and see that number "4" is present. It should then bypass "period1" "period2" & "period3" and insert the number £312.00 into "period4" onwards.

 

Likewise if period said 8, it should bypass "period1" to "period7" inclusive. and start inserting in "period8"

 

I would appreciate it if anyone could help

 

melvyn.parr@morleycharles.co.uk

[Horsepwr]

The following will work but there may be more efficient ways. I used only 6 fields but you get the idea

 

Clear [ p1 ]

Clear [ p2 ]

Clear [ p3 ]

Clear [ p4 ]

Clear [ p5 ]

Clear [ p6 ]

If [ period = 1 ]

Insert Text [ p1, "312" ]

Insert Text [ p2, "312" ]

Insert Text [ p3, "312" ]

Insert Text [ p4, "312" ]

Insert Text [ p5, "312" ]

Insert Text [ p6, "312" ]

End If

If [ period = 2 ]

Insert Text [ p2, "312" ]

Insert Text [ p3, "312" ]

Insert Text [ p4, "312" ]

Insert Text [ p5, "312" ]

Insert Text [ p6, "312" ]

End If

If [ period = 3 ]

Insert Text [ p3, "312" ]

Insert Text [ p4, "312" ]

Insert Text [ p5, "312" ]

Insert Text [ p6, "312" ]

End If

etc......

[CobaltSky]

Here are two alternative (and somewhat more efficient) approaches:

 

METHOD 1:

Based on what you've said, why not change the fields 'period1' to 'period16' to calculating fields, with the formulae set as follows:

period1 = Case(IsEmpty(period), "", period = 1, Value, "")
period2 = Case(IsEmpty(period), "", period <= 2, Value, "")
period3 = Case(IsEmpty(period), "", period <= 3, Value, "")
period4 = Case(IsEmpty(period), "", period <= 4, Value, "")
period5 = Case(IsEmpty(period), "", period <= 5, Value, "")
period6 = Case(IsEmpty(period), "", period <= 6, Value, "")
period7 = Case(IsEmpty(period), "", period <= 7, Value, "")
period8 = Case(IsEmpty(period), "", period <= 8, Value, "")
period9 = Case(IsEmpty(period), "", period <= 9, Value, "")
period10 = Case(IsEmpty(period), "", period <= 10, Value, "")
period11 = Case(IsEmpty(period), "", period <= 11, Value, "")
period12 = Case(IsEmpty(period), "", period <= 12, Value, "")
period13 = Case(IsEmpty(period), "", period <= 13, Value, "")
period14 = Case(IsEmpty(period), "", period <= 14, Value, "")
period15 = Case(IsEmpty(period), "", period <= 15, Value, "")
period16 = Case(IsEmpty(period), "", period <= 16, Value, "")

The calculations may be unstored. Whether unstored or not, they will always instantly update to reflect the status of the 'period' and the 'Value' fields for the current record. This method has the advantage that there is no need to run the script - the data will always be there when required.

 

This method also assumes that you have no requirement to manually edit the values period1...period16.

 

METHOD 2:

If you have reasons for preferring a solution which is based on a script, perhaps the swiftest and most effective sequence of steps would be:

+ Set Field ["period1", Case(IsEmpty(period), "", period = 1, Value, "")
+ Set Field ["period2", Case(IsEmpty(period), "", period <= 2, Value, "")
+ Set Field ["period3", Case(IsEmpty(period), "", period <= 3, Value, "")
+ Set Field ["period4", Case(IsEmpty(period), "", period <= 4, Value, "")
+ Set Field ["period5", Case(IsEmpty(period), "", period <= 5, Value, "")
+ Set Field ["period6", Case(IsEmpty(period), "", period <= 6, Value, "")
+ Set Field ["period7", Case(IsEmpty(period), "", period <= 7, Value, "")
+ Set Field ["period8", Case(IsEmpty(period), "", period <= 8, Value, "")
+ Set Field ["period9", Case(IsEmpty(period), "", period <= 9, Value, "")
+ Set Field ["period10", Case(IsEmpty(period), "", period <= 10, Value, "")
+ Set Field ["period11", Case(IsEmpty(period), "", period <= 11, Value, "")
+ Set Field ["period12", Case(IsEmpty(period), "", period <= 12, Value, "")
+ Set Field ["period13", Case(IsEmpty(period), "", period <= 13, Value, "")
+ Set Field ["period14", Case(IsEmpty(period), "", period <= 14, Value, "")
+ Set Field ["period15", Case(IsEmpty(period), "", period <= 15, Value, "")
+ Set Field ["period16", Case(IsEmpty(period), "", period <= 16, Value, "")

Based on what you've said are the requirements, both methods might suit, however the first has two potential advantages in that:

 

A: it will be fully automatic, saving the user any necessity to run the script on each record (and potentially reducing errors that could occur if the user forgets to run the script or omits to re-run the script after updating either the 'period' or 'Value' fields), and

 

B: if the calculations are specified to be unstored, the first method will reduce the size of the database.