Compare Dates in Portal Rows

[Peter Lehrack]

Helpful People,

I am looking for a way to do a calculation to compare a portal row date field with the previous portal rows date field.

Each portal row has an expiration date, I want to do a calculation to make sure that the current row does not expire before the previous row does. The records in the row are not necessarily entered sequentially so their record ID's are not in order.

Any thoughts?

Peter Lehrack

[Peter Lehrack]

I guess my dream function would be:

GetPortalRowField ( fieldName ; portalRow)

Peter

[comment]

Have a look at the GetNthRecord() function. However, I am not sure I understand the basics here:

The records in the row are not necessarily entered sequentially so their record ID's are not in order.

What then determines the "previous row"?

[Peter Lehrack]

The row above. New rows are not created one after another initially, they are added as needed making the record ID's non sequential.

Added Expires Item

1/1/11 1/3/11 Widget 1

1/2/11 1/6/11 Widget 2

1/2/11 1/5/11 Widget 3

1/3/11 1/7/11 Widget 4

In the above example, I would like a conditional format to trigger via calculation on row 3 of the portal as it expires before the Widget on line 2 does.

Peter

[Fitch]

GetNthRecord ought to do it, but I believe it looks at the sort order of the relationship, not the sort order of the portal. So you might need to create a sorted relationship if you're not already using one.

[comment]

What then determines the "previous row"?

The row above.

LOL. I believe Vaughan will tell you it works differently in Australia...

GetNthRecord ought to do it

IIRC, it won't - not for conditional formatting in a portal.

[Peter Lehrack]

LOL. I believe Vaughan will tell you it works differently in Australia...

The row furthest from the equator? ;-)

[Vaughan]

The row furthest from the equator?

No, the row closest to the bbq. :D

[Vaughan]

I try to think of a "relational" way to do things, so that the data takes care of itself.

Following on from "the row above determines the row below" discussion, a very robust method would be for each portal related record to have uniqueids, and a field that holds the id for the "previous" records. A Scripted process could manage the entry of these values. A relationship can be set up so that each record can see the "previous" value.

[comment]

a very robust method would be for each portal related record to have uniqueids, and a field that holds the id for the "previous" records.

It doesn't seem very robust if records are added out of sequence. This is of course assuming there is a sequence - other than podal/antipodal...

[Vaughan]

Comment, the interface would need to provide a scripted method to add new records and "re-order" them into sequence, which would involve setting the "previous" id field. Any other method would produce results that would be at the mercy of the sort order.

[Peter Lehrack]

I can certainly come up with a scripted solution, I was trying to avoid that though. I may have to just keep dreaming about GetPortalRowField ( fieldName ; portalRow). Seems like a logical function for FM to have.

Peter

[comment]

Comment, the interface would need to provide a scripted method to add new records and "re-order" them into sequence, which would involve setting the "previous" id field. Any other method would produce results that would be at the mercy of the sort order.

Any method here is "at the mercy of the sort order". However, a scripted method would also require modifying existing records. And it would have to be run when an existing record is modified as well. I'd rather have the records sort themselves automatically, using a sort order set for a relationship.

[Fitch]

GetNthRecord does work but without additional caffeine the only way I can come up with is to hard-code a series of conditional format formulas. This may be good enough if we're not talking about infinite portal rows. See attached file.

portalFormat.zip

[Peter Lehrack]

Great!

Thank you, I got it working with one small caveat. I added a summary field for record count to get the row numbers. Sometimes, summary fields don't update instantly. The conditional format formula uses the summary field to select the row for GetNthRecord in it's calculation and it works about 75% of the time. When it doesn't work, changing to a different tab, then back or going to the next record, then back fixes it.

Any thoughts on how to calculate a portal row position without a summary?

Peter

[comment]

GetNthRecord does work

Well, it depends on what you call "work". Note that in your demo the portal is not sorted - i.e. it uses the sort order of the underlying relationship. If you sort the portal in descending order, it will highlight records that expire before the record in the NEXT row. This may be the desired result - but then why mess with the portal to begin with?

[Fitch]

Yes, I said in my previous post that it would use the relationship's sort order, which in this example is the record creation order (i.e. unsorted). I think that's what Peter is asking for. I don't know what you mean, "why mess with the portal," I don't think he's asking for that.

[bruceR]

By using script variables and an incrementer variable, you can get the desired effect. See custom function calcs attached to the Item::ID field.

Let( [

$n = $n+1;

$thisDate[$n] = GetAsNumber( item::expires );

$flag = Case($thisDate[$n] < $lastDate; 1; 0) ;

$lastdate=$thisdate[$n]

];

$flag

)

As mentioned on Twitter, I'm wondering how far we can go with conditional formatting calculations.

I suggest that it may be possible to drop objects with conditional formatting calcs, even a long series of calcs, onto layouts and have very portable programming objects.

portalFormatCF.fp7.zip

[comment]

I don't know what you mean, "why mess with the portal

I mean that this is not going to be solved at layout level alone. When you are doing conditional formatting in a portal, there is no way to get the N for GetNthRecord() from the portal row number.

You could define an unstored calculation in the child table =

Get (RecordNumber)

and use that for the N. This will actually respect the portal's sort order - even filtering.

I said in my previous post that it would use the relationship's sort order, which in this example is the record creation order (i.e. unsorted). I think that's what Peter is asking for.

I don't know what he's asking for. I am still waiting for an answer to my question...

[Fitch]

When you are doing conditional formatting in a portal, there is no way to get the N for GetNthRecord() from the portal row number.

Well OK, but if the portal isn't sorted or filtered, then N is the same thing as the row number, so who cares if we got N "from the row number"? The point is, we got it.

[Fitch]

That is really interesting, Bruce.

It reminded me of this article from a few years back:

http://web.mac.com/zueiv/iWeb/FileMaker/Script%20Killing%20Techniques/Script%20Killing%20Techniques.html

[Peter Lehrack]

Well OK, but if the portal isn't sorted or filtered, then N is the same thing as the row number, so who cares if we got N "from the row number"? The point is, we got it.

The portal is not sorted or filtered. I am currently getting 'N' from a new summary field, the only issue with it is that summary's are not always calculated. Users at times will have to go to the next record and then back for the summary to update.

Peter

[bruceR]

That is really interesting, Bruce.

It reminded me of this article from a few years back:

http://web.mac.com/zueiv/iWeb/FileMaker/Script%20Killing%20Techniques/Script%20Killing%20Techniques.html

Thanks!

Yes, I think there is a lot yet we can do with conditional format calcs.

I wonder if anybody is using the Relator technique?