Complex calculation for determining the value of a field

[and13345]

I am trying to put a date in a field that is a calculation based on the values of 3 other fields. The filed name is "next_svcdate". This field is calculated by starting with the field "svcdate" and then generate a new date based on the "svctype", "week" & "dayofweek". The "svctype" contains values for Daily, Weekly, Monthly, Simi-Monthly, etc. The "week" field contins what week in the month the service should occur and the "dayofweek" determines what day (Sunday, Monday, Tuesday, etc) the service should occur on.

So if I enter a svcdate of 1/1/13 and the service type is Monthly on the 1st week and on Tuesday, then it should calculate the "next_svcdate" to be "2/5/2013".

I am assuming that I would have to do this as an expression in the Field Rules for the "next_svcdate" what I have that assigned as a calculated field?

Any help or guidance would be greatly appreciated.

[Ray in Capetown]

Re: Complex calculation for determining the value of a field

Week will be ignored or blank for service types daily & weekly .
What is simi-monthly - is that like semi-monthly and meaning partly monthly? Anyway leave that out for now

Take WOM to mean Week of the Month
I would start with calculating the date of precise period, check for a WOM value and if not blank move the date on to that WOM (forward)
Then check if there is a dayofweek value, move on again to that.

The mechanics of that could probably be in a UDF (function) called perhaps by a field rule.

[DaveM]

Re: Complex calculation for determining the value of a field

Semi-Monthly is 2 times a month(usually on set dates or on those but a certain day of the week) like 15the and 1st at each Monday after

You forgot bi-weekly = every 2 weeks on a certain day of the week.

Almost sounds like buy here & pay here in the car business.

[DaveM]

Re: Complex calculation for determining the value of a field

Kieth,
Field rules will never handle all of what you are trying to do. You will have to rely heavy on variables along with scripts, but mostly on some udf's to get this done.

EDIT: Maybe I am over complicating it for you because we had to do this everytime a payment was made for the next one and a field was present for the next or we made a table entry for that customer where the may have as many as 200 payments to post against.

I think the purpose for which you are creating this will help all here to help you. It has been a while since I crossed some of this stuff.

[and13345]

Re: Complex calculation for determining the value of a field

After sleeping on it over night and some more thinking this morning I have found a way to do this. I changed the field rules to make the next_svcdate a user entered field type. On the form I set variables for svcdate, svctype, week & dayofweek. I then modified the OnChange event for all 4 of those form fields to execute a script where I do all the complex calculations to determine the correct next_svcdate. When the script is finished I update the next_svcdate field on the form with the calculated value from the script.

At this point this seems to be working. I just need to finish all the complex calculations and I should be good to go.

[Ray in Capetown]

Re: Complex calculation for determining the value of a field

Semi-Monthly is 2 times a month(usually on set dates or on those but a certain day of the week) like 15the and 1st at each Monday after

You forgot bi-weekly = every 2 weeks on a certain day of the week.

Almost sounds like buy here & pay here in the car business.

I looked it up, you are right. Bi-monthly can mean BOTH strangely enough but semi monthly is as you say twice a month.
bi�month�ly /baɪˈmʌnθli/ adjective
1 : occurring every two months
▪ The group holds bimonthly meetings in January, March, May, and so on.
2 : occurring twice a month : semimonthly
▪ The group holds bimonthly meetings on the first and third Tuesday of each month.

[DaveM]

Re: Complex calculation for determining the value of a field

I had to use this to compute payments based on many different scenarios.

Just a minute portion of one script:

select
	case type = 1 'weekly
		peryr = 52
	case type = 3 'monthly
		peryr = 12
	case type = 2 'biweekly
		peryr = 26
	case type = 4 'simi-month
		peryr = 24
	case type = 5 'quarter
		peryr = 4
end select

There is also semiannual and annual, which I never had to use

[StephenP]

Re: Complex calculation for determining the value of a field

I just threw this together. Obviously you'll have to tweak it. But save it as a UDF and you can start playing with it.

FUNCTION NextServiceDate AS D (ServiceDate AS D, ServiceType AS C, ServiceWeek = 1, ServiceDay = -1 )
	'error checking (look for invalid date, type, etc.)
	if ServiceDate = {} then
		'add error checking here
	end if
	
	if ServiceType = "" then
		'add error checking here
	end if
	
	if .not. between(ServiceWeek, 1, 5) then
		'add error checking here
	end if
	
	if .not. between(ServiceDay, -1, 6) then
		'add error checking here
		'the -1 indicates that the same day of the week should be used
	end if
	
	ServiceType = ut(ServiceType)
	
	select
		case ServiceType = "DAILY"
			NextServiceDate = ServiceDate + 1
		
		case ServiceType = "WEEKLY"
			if ServiceDay = -1 then	'-1 indicates that we should use the same day of week
				NextServiceDate = ServiceDate + 7
			else					'otherwise use day of week provided (0=Sun, 1=Mon, etc)
				NextServiceDate = ServiceDate - dow(ServiceDate) + 8 + ServiceDay
			end if
		
		case ServiceType = "BI-WEEKLY"
			if ServiceDay = -1 then	'-1 indicates that we should use the same day of week
				NextServiceDate = ServiceDate + 14
			else					'otherwise use day of week provided (0=Sun, 1=Mon, etc)
				NextServiceDate = ServiceDate - dow(ServiceDate) + 15 + ServiceDay
			end if
		
		case ServiceType = "MONTHLY"
			if ServiceDay = -1 then
				ServiceDay = dow(ServiceDate)
			end if
			
			dim DaysofWeek as c = "Sun,Mon,Tue,Wed,Thu,Fri,Sat"
			NextServiceDate = nth_dow(word(DaysofWeek,ServiceDay + 1,","),month(addmonths(ServiceDate,1)),year(addmonths(ServiceDate,1)),ServiceWeek)
		
	end select
	
END FUNCTION