Posting a conditional value from 1 table to another

[Hawky]

Hi, we have a database that consists of a number of tables such as Clients, Billing, Inspections, etc.

The billing table simply records date, amount and how many months of additional monthly servicing they require.

As the database is setup now the client form has a field that records their final month inspection date.

When someone pays for more inspections we have to manually update the field in client table to record when their subscription expires.

Is there some way for this to be done automagically? So that when we enter 3, 6 or 12 months of additional inspections it updates the field in the client table? I'm sure there is but I just can't seem to get my head around exactly how to make it happen.

Any advice or suggestions would be very much appreciated.

Thanks in advance
Greg

[Stan Mathews]

Re: Posting a conditional value from 1 table to another

There are several possible ways to do this but I don't think you've given enough info.

when we enter 3, 6 or 12 months of additional inspections

Is this what you do, enter a numeric 3 or 6 or 12 in some field? Do you create a new record for the additional service or do you modify the previous value to indicate the longer period?

[Hawky]

Re: Posting a conditional value from 1 table to another

HI Stan and thanks for taking a look!

OK well we run a set made up of 5 different tables. The two problem ones are the billing and the client data tables.

The client data table has a field that stores the date of the last inspection for that particular client.

The Billing table shows the client ID, payments made with a date of payment, amount and the period of either 3, 6 or 12 additional months. So we can use that figure of 3, 6 or 12 as the basis for the calculation.

The only area I'm having a problem with is how to get the billing field to update the last inspection field for that client. I'd imagine it will be a post or append function but as I said in my original post I'm just having trouble getting my head around it.

Thanks again for any suggestions or input.

Regards
Greg

[Tom Cone Jr]

Re: Posting a conditional value from 1 table to another

Greg, Stan will probably have a better idea, but here are two possibilities to get the ball rolling:

1) Add fields for the current date of last inspection and the new date of last inspection to the Billing table. Populate the current date of last inspection using a calc field rule to retrieve the field value from the client data table (using a lookup() function). Use a calc field rule in the Billing table to compute the new date of last inspection. The AddMonths() function will come in handy here. Then use a post field rule on the new date of last inspection field to "replace" the date of last inspection field value in the client data table.

2) Write a custom script in the CanSaveRecord event in field rules for the Billing table that opens a new instance of the client data table, fetch_finds the desired record, computes the new date of last inspection field value and then writes it to the record before closing the client data table.

The first approach involves the least amount of coding but would store redundant data in the billing table. The second approach doesn't store any redundant data but would involve more coding.