Hi Pam

Option 1 does the following select

SELECT CHARGES BY PROPERTY_GROUP BY PROPERTY_NO_SORT BY TENANT_SYSID BY TENANT_ID_SORT BY TRANS_NO

Option 2 does this select

SELECT CHARGES BY TENANT_TYPE BY TENANT_ID_SORT BY PROP_SUBURB BY PROP_STREET_SORT BY PROPERTY_NO BY LEASE_NO_RIGHT BY INVOICE_NO_LIST BY-DSND CHARGE_OVERDUE BY DUE_DATE BY TRANS_NO

When option 1 saves the active list I get a normal list of charge keys

When option 2 saves the active list I get a strange list of not quite charge keys ie something like

readnext key

gets me

key=chargekey

key=value in field 1 of charge

eg

12356 @VM 2

23899 @VM 1

instead of

12356

23899

naturally, in this case my key is not a key to the charge file and the whole program/report falls over in a heap. Why are there not quite keys in there?

Is it related to some of the sort fields being multivalued?

Is it something else, like could it have to do with one of the fields being multivalued (option 2 also does a select on that field not being empty).

If so why does the same kind select work properly in another program.

Has anyone else had problems like this before. I feel like I am missing something obvious.

Scott

Scott

This is a feature . Basically if you sort by a multi-value it explodes the multi-value so each mv appears once in the final result. OI realises that you might have associated MVs and kindly tells you which MV number this particular on is - hence the extended ReadNext Id,WhichValue syntax.

The Sprezzatura Group

World Leaders in all things RevSoft

Hi Sprezz

So if I code

readnext charge_id then

charge_id=charge_id

else done

that should solve the problem, since I don't care which multivalue is associated.

It's a wonder any of my stuff works at all.

And I will never make another post while talking on the phone about something else. Fortunately I had composed the technical part of that post before the phone rang.

Scott

Yup - or just recode ReadNext ChargeId, WhichValue and discard the WhichValue… and I'll try to spell one properly…

The Sprezzatura Group

World Leaders in all things RevSoft

Well maybe ..

You're likely to see the same key twice though, one for each MV in the select statement. Unless you put a stamp field to tell yuu that you've been there already this pass, you are likely to process each record multiple times. If you don't need it, why do an exploding multi-value sort at all? E.G., it's the BY clause that's giving you trouble. If you only need to have the SELECT clause isolate specific records that contain some value in the MV chain, a WITH clause will do this for you.

Don Miller

C3 Inc.

Hi Don

Uh, I have both a with clause, and a sort clause. And I need both.

I have to get all the charges that have been "invoiced", and then sort them into invoice order. To complicate matters, they can be on more than one invoice although that is unlikely. Looks like I had better change the fields again. I do have a calculated field to get me just the latest invoice only, maybe I could sort by that, but the with clause still got me double keys, ie when I took off the sort clause I still got two values, ie the mv pointers.

Sigh, now I have to check I am not getting charges listed twice.

Scott.

Scott ..

It sounds a little convoluted, but you might be able to do it with a bit of a symbolic. The idea would be to set up a global variable @USER0 - @USER4 that contain the date range you're looking for. It would populate symbolics containing only the items in that date range in the order you want (presumably by date). What you would get then is a sorted list by customer with the desired invoices in sorted date order. When you populate the list you can internally sort the MV's into any order you want using a LOCATE function. It's not terribly fast, but is quicker than an exploded multi-value sort which returns the @ID,Which_Value thingie. Maybe this will help you.

Don Miller

C3 Inc.

Hi Don

I found and fixed two problems

1. I changed all the selects/sorts to be based on the latest invoice only, as even though a charge might appear on more than one invoice, the client is only required to pay it once. It was listing the charges twice before that and it wasn't sorting properly.

2. I had a problem with my report break totalling which meant I had to combine two fields to break on, instead of one. Ie a tenant could have more than one invoice, but the invoice could have more than one tenancy on it, and it was only reporting the first invoice-tenancy combination when it should have been going tenancy A - invoice 1; invoice 2; invoice 3 then tenancy B invoice 1; invoice 2; invoice 3.

I hope it is all working now. I can't believe people I had been working with, who are very familiar with OI and Arev didn't warn me about problem number 1. I should have thought it through at the time.

And I can't believe our testing didn't pick up problem number 2.

Yikes.

Scott

One, doing the work of four.

Scott ..

I'm glad you solved the problem. Associated multi-values are fine in terms of data entry and the like, but reporting is sometimes a PITA. My healthcare billing system is similar: Invoices are ordered by Payor-source*patient, with dates of service and charges as line items. Payments from the payor are a single check sorted by patient then by date of service then by service. In order to reconcile this, I build a temp file with the amv's converted into single-value flat file structures and then stuffed into a popup. The operator selects the items that are being paid and amount being paid. Then saving the payment updates the outstanding invoices and rebalances the A/R items accordingly. Agh!!

Don Miller

C3 Inc.

View this thread on the forum...