Automatic portal filter

[Angus Murray]

I have a similar problem to the recent threads on filtering information in a portal.

I have an 'invoices' table that contains a portal showing 'extras' that are in a related table ("Extras ID = Invoices ID") but different (remote) fm8.5 file. All the 'extras items' show up as expected in the portal. However, I need the portal to show only the unpaid 'extras items' for each client. Using a 'date received' field in the 'extras' table, I have set up a global calculation field ( 'gfilter') as follows:

Global,= isEmpty (extras: Date_received)

My question is:

How should I proceed from here? Because 'invoices' and 'extras' are in different files, will a global field actually work, and if not, how can I get round this?

Grateful thanks in advance for any suggestions - this has had me puzzled for a while now.

[LaRetta]

Hi Angus Murray,

I would not recommend joining ExtraID to InvoiceID. I assume that might be a mistake but you should have an InvoiceID in your Extras table (as the foreign key) and join Invoices::InvoiceID to Extras::InvoiceID.

But to your question (assuming you have an InvoiceID in extras)

Create another occurrence of your Extras table (maybe called unpaid Extras) and establish the relationship as:

Invoices::InvoiceID = UnpaidExtras::InvoiceID

AND

Invoices::InvoiceID > UnpaidExtras::DatePaid

In this way, each unpaid Extras portal (on an Invoice or Client layout) will only display those Extra items who do NOT have a date paid in them and no global in Invoices is needed.

UPDATE: I'm unclear whether these unpaid extras should show up in your Client layout or your Invoices layout. Adjust as needed.

LaRetta

[comment]

Does this work for you, matching number to a date?

[LaRetta]

InvoiceID is number. Date paid is date. Yes, it works in 8.0v3 and 8.5. I haven't tested it in v9. :)

[comment]

Can you post your file? I can't make it work (8.5 or 9).

[LaRetta]

Ah. It can break. Extreme example - InvoiceID 8999999 and date 1/1/0001.

But worse than that ... when I add a calculation cConstantOne with simple number 1 and use it in place of the InvoiceID in > date, it doesn't display ANY records. In other words, using the InvoiceID, InvoiceID 1 works and constant 1 BREAKS. (please see attached). Note that InvoiceID 1 displays unpaid records. Then change the relationship to using the constant 1. It breaks entirely.

It was poor logic of mine ... thinking that ANY invoice number would be greater than an empty date field; and not considering that a very large invoice number could still be greater than a current date (number).

What throws me now is that InvoiceID 1's unpaid entry will disappear when I change the line to using the constant one instead of the InvoiceID - both are numbers.

LaRetta

invoiceextras.zip

[comment]

a very large invoice number could still be greater than a current date (number).

That's what occurred to me first. But then I tried and couldn't get it to work at all (using a plain number field opposite a date field). I can't see any apparent logic in this. It certainly breaks Shawn's assumption that the 'local' matchfield value is converted to the type of the 'remote' matchfield. The only conclusion I can draw is that experimenting with mis-matched field types is risky. I would go for a conservative solution: a calculation field in Extras =

Case ( not DatePaid ; InvoiceID )

and match that (alone) with the InvoiceID in Invoices.

[LaRetta]

Well, to provide a consistent always-working solution, create a calculation called cUnpaidExtra in your Extras table. It would be simply:

Case ( not DatePaid ; InvoiceID )

Then the relationship would be:

Invoices::InvoiceID = cUnpaidExtra

This is assuming your DatePaid is a true date. If not, it should be. I've used comparative relationships before, joining to empty fields. But it won't work here because we (as Comment questions) are joining number to number.

I'm still questioning why my demo works in apparently inconsistent fashion.

UPDATE: I was busy typing my response and didn't see your post. Yes, I assumed it worked because dates are numbers anyway to FM. But do you see the problem in my demo file on why it breaks with constant but not with the ID?

LaRetta

Edited August 5, 2007 by Guest

[comment]

All I have is an unsupported wild guess: with the ID field, it already has an index, created to deal with a number on the opposite side. But if there's no index yet, and the opposite field is a date, it goes crazy.

FWIW, if you type a date into the number field, it will find a matching date - seems it matches text to text.

[Angus Murray]

Thanks LaRetta

Works like a dream! I would have never got there without your post! Thanks again.

[LaRetta]

You are welcome but ...

I would have never got there without your post!

... and we wouldn't have gotten there without Comment questioning my original suggestion.

Thank you, Michael. :smile2: