Jump to content

Compare Dates in Portal Rows


This topic is 4306 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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"?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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...

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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...

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

This topic is 4306 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.