Anyone out there have an idea of how we can pull this off? This is a two parter.

Part 1.

We have an ?estimate? record that contains, in an edit table, the following multi-valued fields - a code, symbolic field (which is the description of the code) and the estimate amount. Naturally, there can be many of these codes. Note - I use the word table in place of multi-valued fields - it?s easier to type. All the fields in the table are multi-valued.

An example of the estimate table:

code description amount

ec who knows $123,000

fc I don?t know $23,000

eg who cares $13,000

When the estimate record is first created, we wish to write a billing record that contains the same table information (the code, symbolic field (which is the description of the code) and the estimate amount) in addition to other non-table information.

An abbreviated example of the billing record when the estimate is first created:

code description amount 1st bill 2nd bill total amount left

ec who knows $123,000 0 0 0 $123,000

fc I don?t know $23,000 0 0 0 $23,000

eg who cares $13,000 0 0 0 $13,000

What is the easiest way to accomplish this? We have created a billing record from other estimate records with no problem, however, those records did not have the table included.

Part 2.

The first billing comes in:

code description amount 1st bill 2nd bill total amount left

ec who knows $123,000 $23,000 0 $23,000 $100,000

fc I don?t know $23,000 $45,000 0 $45,000

eg who cares $13,000 $7,500 0 $7,500 $5,500

A revised estimate is received after the first billing.

Revised estimate:

code description amount

ec who knows $123,000

fc I don?t know $50,000

eg who cares $13,000

After the new estimate is written, the billing record needs to reflect those changes:

code description amount 1st bill 2nd bill total amount left

ec who knows $123,000 $23,000 0 $23,000 $100,000

fcI don?t know $50,000 $45,000 0 $45,000 $5,000

eg who cares $13,000 $7,500 0 $7,500 $5,500

We know how to handle the amount left, that?s not the problem. The problem is how do we change the estimated info on the billing record without destroying any existing billing data?

Thanks

Andy - Illinois State Lottery

Hi Andy

I am having trouble figuring out what your question is but that never stopped me from having a go at answering.

Summary, have a look in the manuals for LOCATE command, .LIST and .ARRAY edit table properties and building calculated columns using Table builder.

Scott, LMS

I am guessing here about structure but I think this is what we have on the bill:

code_field: ec @vm fc @vm eg

so if I want the fc code it is at position 2 ie code_field

if I want to figure out what position it is at in the list I use LOCATE command

ie

this_code=fc"

LOCATE this_code IN code_field USING @VM SETTING this_code_ptr

ie this_code_ptr will get set to 2 in this example.

I am assuming that the rest of your fields are set up in the same way as the code field, multivalues so would look a bit like

desc_field: WhoKnows @VM Idunno @VM WhoCares

(note ms word makes the quotes go funny when you do the cut and paste)

estimate_amt: 123000.00 @VM 23000.00 @VM 13000.00

1st_bill: 23000.00 @VM 45000.00 @VM 7500.00

etc

So when the estimate for code fc changes from 23000.00 to 50000.00

you run a program which finds all the fc occurrences in the code field and replaces the corresponding amount in the estimate amount.

If it was me, the estimate amount would be a calculated (symbolic) field which takes the code field list and uses it to look up the amounts, so the instant you changed the amount on the estimate record it would be reflected in any reporting of the bill data.

Have a look also at the difference between .LIST property and .ARRAY property, one of them makes doing a locate much easier.

The other thing you might want to learn more about is the symbolic fields or calculated fields (or I-Types as I used to call them with PI-OPEN) - have a look in the using open insight native tables for creating calculated "columns" with table builder.

Thanks Scott - you have given us some good ideas.

We'll give them a shot.

View this thread on the forum...