lookup based on what?

[-Queue-]

Here's my situation. I have an unstored calculation field that's based on two conditionals. I also have an unstored Get Field calculation that grabs the other calc's data. Now, I want to have a third field (that I can index) lookup the information from the Get Field field. The problem is that one of the conditionals is based on a field from another file. And I can't figure out how to auto-force the lookup when the related field changes.

In plain English, whenever the Billing Month changes on the master file, I have a sort value change in the pre-invoices file, based on whether a single charge's Billing Month matches the one from the master file: '1' if it doesn't match, and '0' if it does. I want to concatenate the pre-invoice number and this result into an indexable field, so I can display only those charges which are 'valid', i.e. whose pre-invoice number and Billing Month matches the master file's. Then I can display the valid charges for a particular pre-invoice in a portal for modification.

I'll probably feel like a moron once I have the answer. But I don't want to spend too much time on development, if I can use other brains to speed it up.

TIA

[Ugo DI LUCA]

-Queue- said:

The problem is that one of the conditionals is based on a field from another file. And I can't figure out how to auto-force the lookup when the related field changes.

Aren't we all looking for this ? Unless this related field is brought to the Main and indexed, you will be stuck here I think. How exacly is this conditional set up ?

[Ugo DI LUCA]

Sorry Queue, I missed the second part of your post

based on whether a single charge's Billing Month matches the one from the master file: '1' if it doesn't match, and '0' if it does.

I think this is the key...

If only one record needs to be changed, that would simplify the process.

I remember having solved that one in my Product-LineItem-Inventory using a 4th calc involving a Case Statement with the Max Function, returning the Product_ID, and getting the value looked up into an indexable field.

I'll try to be more specific if I can or if you need, or if this make sense.

[-Queue-]

cSort := not BillingMonth = Master::BillingMonth or flag_delete

flag_delete is manually changed, so that one is not a problem. When the Billing Month is changed, however, is when I run into issues.

I supposed my alternative is to force a lookup whenever the master Billing Month is changed, but this could effect thousands of records, and I don't want to make my users wait indefinitely for it.

There must be some sort of trick I can use. I'll work more on it later.

Thanks, Ugo.

[-Queue-]

I should have said 'individual' instead of 'single'. Each record in the file updates its cSort field when the conditional is met. So I need to somehow reindex them all each time it changes.

[Ugo DI LUCA]

Oh well.

So I'll bookmark this thread and catch any magician

True this would be a real challenge and a straight path forward.

[Ugo DI LUCA]

JT,

After reading your post again, I think I finally understood what you were into. I'm not convinced though the GetField would be of any help here, and I'm quite sure a script is the best you could get for the job.

You want to display the current matching keys in a portal based upon an unstored calculation.

A workaround would be to switch from a Classic Portal to a Filtered with relationship Master:t_TempIds::Related:Unique_ID with a little script.

1. Reversing your boolean c_Sort to c_Sort2 = Case(Billing Month = MasterBillingMonth,1,0)

2. Using your relationship MasterRel = MasterPreInvoiceN