Start and End Values

[Mortie]

The attached images is an embedded browse from a child table in a set.

In this particular table there will never be more than 12 records and at the end of 12 months these records will be stored (appended) to another table. Hence after December, the year starts all over.

In Jan I want to capture the PIF value and place it "somewhere" that it can be used as a value in a calculation. This number should remain constant throughout the 12 months

Starting in Feb, and in each subsequent month through Dec, I want to capture the a new value in the PIF column and store it "somewhere" to be used in a calculation with the Jan value. I do not want this value to change until he next month (record) is added to the browse. The new month can replace the old value. Also, if the value in the field were to change for that given month I would like it to update also.

The calculation would be the latter value minus the former value, hence a net gain is determined.

I have tried the use of variables and posting to a child table even but I cannot figure out how to really make the changing value portion work.

Help is appreciated

[Peter.Greulich]

Re: Start and End Values

In Jan I want to capture the PIF value and place it "somewhere" that it can be used as a value in a calculation. This number should remain constant throughout the 12 months

Store it in a 2nd table, perhaps?

Starting in Feb, and in each subsequent month through Dec, I want to capture the a new value in the PIF column and store it "somewhere" to be used in a calculation with the Jan value.

Store it in the same 2nd table, two fields?

I do not want this value to change until he next month (record) is added to the browse. The new month can replace the old value. Also, if the value in the field were to change for that given month I would like it to update also.

So, if I understand you, replace the 2nd value in the 2nd table as needed?

The calculation would be the latter value minus the former value, hence a net gain is determined.

So, 2nd table: Field2-Field1; where:
Field1 remains static for the year and Field2 changes as needed.

If that is the case, I don't think you need to make it be a child table, just a table "linked" w. xbasic. It's hard to understand your scenario completely - e.g., where do the calc results display?

[Mortie]

Re: Start and End Values

Store it in a 2nd table, perhaps?

I have tried this but cannot seem to get the posting to work. I can get the start (Jan) value to post (in a field called Start_Pif but have not been able to figure how to get the last record entered to post in a second field, End_Pif.

So, if I understand you, replace the 2nd value in the 2nd table as needed?

Yes, replaced when a new month is created by adding another record in the table/fields shown in the browse. Also it should change if the last created record in this table is changed/edited

So, 2nd table: Field2-Field1; where:
Field1 remains static for the year and Field2 changes as needed.

Yes, and in the case of the browse in the image it would be

470-389, which is 81.

If that is the case, I don't think you need to make it be a child table, just a table "linked" w. xbasic. It's hard to understand your scenario completely - e.g., where do the calc results display?

I want to display the net number (389-470) on the same form as the browse in the image. There are several other browse on this same form; once I know how to do this calculation I can complete it for the remaining data in different tables.

"linked" w.xbasic is not something I have dealt with before.

[G Gabriel]

Re: Start and End Values

Bob:

these records will be stored (appended) to another table.

This seems hardly necessary as the original table only has 12 records. But if you have to do that for some other reasons, then use lookup() expression to lookup the values from the second table.

I believe this could also be accomplished with variables and posting, but the expression seems to be the simplest approach.

To expand on my first comment:
What happens after the second and subsequent years?

After the first year, you appended 12 records to the second table.
What happens after the second year? do you append another 12?

If so, this mean the second table will have 24 records, the first 12 relate to the first year, the second 12 relate to the second year. And if so, there must be some other identifier for the year. And if so, why can't you keep all the records in the original table and use that identifier of the year?

Or, will you overwrite the records of the first year with those of the second year?
And if so, what is the point of having two tables each holding a max of 12 records?

[Mortie]

Re: Start and End Values

This seems hardly necessary as the original table only has 12 records. But if you have to do that for some other reasons, then use lookup() expression to lookup the values from the second table.

The only reason I have a second table is because that is the only way I could figure out the posting process. I believe now I can at least get the first record to post to an additional field in the data table. If I could figure out how to get the value into a second field I would do as you suggest, use one table.

I believe this could also be accomplished with variables and posting, but the expression seems to be the simplest approach.

I agree but cannot yet figure out how to get that new/last record value into a variable that I can then post to the other field. That is the insight I need.

After the first year, you appended 12 records to the second table. What happens after the second year? do you append another 12?

Yes that is correct. There will be no posting of data to the archive table though

Ultimately this is a sales record/sales report card application. The end result is to be able to compare current year with any previous year and try to do it on one form versus loading cascading pages. Hence I felt it was best to enter and display current year records from one table and use a different table(s)/set(s) to make comparisons. This is probably bulky but given my plodding way of developing an application (for my own work efforts) it is the best I could come up with for now.

[G Gabriel]

Re: Start and End Values

Bob:
Rather than providing you with an example that might not co-incide with your design, it would be best if you can zip your DB.

[Mortie]

Re: Start and End Values

Gabriel

I have been doing my work in V8. Will that work for you?

[G Gabriel]

Re: Start and End Values

It should.

[Mortie]

Re: Start and End Values

here is my db as of today.

only one primary table record and 12 records in the child tables. The only tables that will be used as to posting are auto and fire. The others do not need this process.

Thanks for our help

[G Gabriel]

Re: Start and End Values

Bob:
Taking a quick look at your DB, you have a separate table for each policy type then all linked in a set to the agent.

You only need two tables:
One for Agents,
The other for policies.
Add to the Policy table a field to identify the policy type whether life, Auto etc.
Once you have that, then you could use dbcount() & dbsum() to calculate the number of policies for each agent for each year.

[Mortie]

Re: Start and End Values

Bob:
Once you have that, then you could use dbcount() & dbsum() to calculate the number of policies for each agent for each year.

Thanks Gabriel for the information. I don't think the suggestion fits the application though. It is very tough, I know, for developers using the forums to look at a business need application and grasp the terms, processes, purpose, etc. The number in the auto and fire PIF field is already a total number for that particular policy type and does not needto be summed or totaled. My objective is to calculate the gain (or loss) between the start of the year (Jan) and each subsequent month through Dec. The NB fields I do use a calculated value to sum the totals for the year. hence, the PIF field is really the only one I need Net numbers for.