Use data from previous record

Posted by: Lasse Lehtinen

Use data from previous record - 01/05/09 04:38 AM

Lets say we a database like this:


Is it possible to store previous database fields in variables if a certain field does not change? I already found this macro that checks if a value changes, but I would also like to fetch data from previous record.

To make things even more complicated, the amount of repeating customer_id can be anything from 1 to n. And of course I should print only the last customer_id containing all the data.

Is thing beyond PSM scripting? I could ask the database provider to group the records as one or maybe I will roll my sleeves and write a program that parses through the data and appends the data of previous record to next one if these criterias are met.
Posted by: Anonymous

Re: Use data from previous record - 01/05/09 06:45 AM

Posted by: Anonymous

Re: Use data from previous record - 01/05/09 06:47 AM

In addition to the macro: it reads or stores data in each record it currently is, so be aware that data is overwritten if you chose the same position.

Storing data into the current record number makes it easier to create a formula to pull the wanted data.
Posted by: P. Brittle

Re: Use data from previous record - 12/10/09 04:48 PM

Could this macro be altered for referencing tables of data?
For example, I want to replace the state abbreviation with the full state name.
The macro would go to an external file with a state table and replace it.

I could (and have) built tables like this using if-then statements in a variable inside PSM. The problem is that the tables are used in several places using different criteria.

The goal here is to have one place where I can change the table's data without having to change each variable in the PSM project.
Posted by: JT

Re: Use data from previous record - 02/10/10 07:32 AM

Is this possible in PSM7? I have a job now that could really use this function.
Posted by: Raphael Lalonde Lefebvre

Re: Use data from previous record - 02/10/10 09:34 AM


This particular aspect hasn't changed, you cannot take data from the previous record. It may be possible through macros, as Floris suggested above. However, macros aren't officially supported and can be unreliable, so if you use them, you do so at your own risks.

There is, however, a new CHANGED command that you can use to verify if the new record has changed from the previous one. So you could use something like this:

IF(CHANGED([MyField]), "The field has changed.", "The field has not changed.")

It would display "The field has changed." if it did change, or "The field has not changed." if it hasn't.

Hope that helps!

Posted by: JT

Re: Use data from previous record - 02/10/10 11:12 AM

Thanks, I had a look at the CHANGED expression but the problem is I need to grab the data from the fields on a skipped record.

I would be happy to try this macro but having created a macro folder and copying the 'js file I can't use it, what am I doing wrong?