Hi!
in my table I will have a lot of column with the dame date and I need a quick graphic way to distinguish between days. I was thinking to add a colored line between row that has the date field different.
But…I’m just realizing that this will not work because if I change the order of the table it will be a mess.
What could be another way?
Thanks!
Hi Marc,
We can do this by comparing the Date value to the prior record’s Date value. I created an example for you here: Community #2684 - Grist
We are going to use the unique ID that is assigned to each record when the record is created. If you have deleted any records or have any sorts applied, the IDs won’t be in order. We use a helper table to make a list of IDs in the order they appear in the table.
The formula used in the Cumulative IDs column of the Sort table is:
Dates.lookupRecords(sort_by='manualSort').id
You’ll see that the IDs are listed in the same order that the appear in the table.
Next, we’ll need to add a formula column to our original table to store the prior record’s date.
The formula in the Prior Record’s Date column of the Dates table is:
order = Sort.lookupOne().Cumulative_IDs
index = order.index($id)
previousId = order[index-1] if index > 0 else 0
previous = Dates.lookupOne(id=previousId)
return previous.Date
We find the index of the current record’s ID then find the value to the left of this ID in the list of Cumulative IDs. This is our prior record. We then pull the Date value for the prior record.
Now, we can add a conditional row style if the value in the Date column does not match the value in the Prior Record’s Date column.
To add a conditional row style, navigate to the Table tab of the Creator Panel and add a conditional rule under Row Style using the following conditional formula:
$Date != $Prior_Record_s_Date
Be sure to modify the styling to make the row stand out.
We can hide the Sort table from our view by clicking the three-dot icon then ‘Delete widget’.
It will continue to sort IDs in the background so if you add more rows or sorts, the conditional formatting will still correctly apply when a Date does not match the prior row’s date.
I hope this helps! Please let me know if you have any follow up questions.
Thanks,
Natalie
1 Like
hey Natalie
I have a very similar but slightly more complicated question. I have a table, TRADES, that stores trades. In testing the trades are taken in isolation, but in live trading we can only have 1 trade open at a time. So if there is still an open trade, a new trade can’t be placed even if it shows up on a chart.
This is an image from my table. The red arrows skip over trades that are in the TRADES table that wouldn’t have been taken in live trading since another trade was still open.
There are 2 things I need that I didn’t see in the example above.
The first is that my “TRADES” table is filtered for a TradeDate by selecting the date in another table (see image below). So I need a subset of all trades for the date in the TradeDate column.
Second, the math that I think I need to add a column that has the highest CloseTime as you go down the table. I can’t just compare to the previous record CloseTime since that trade may not have been placed. You can see in my example where a trade stayed open long enough that many trades couldn’t have been taken.
All the trades with a check in DblTrade are trades that wouldn’t have been taken in live trading.
So if I could add a column that was the highest running CloseTime, I could compare the open time to it and see if the trade would have been placed. Then I would “auto” check the DblTrade column.
This example got me close, but I know you can get me over the finish line.
As always, thanks for your great help
Mike
Hey Mike!
I created an example for you here: Community #2684 - 2 - Grist
The formula in the DblTrade Formula column is:
all_trades = Trades.lookupRecords(Date=$Date)
for trade in all_trades:
if trade.CloseTime > $OpenTime > trade.OpenTime:
return True
First, we lookup all Trades for that same date and assign the list of Trades to the variable all_trades.
Next, we iterate through the list of Trades to compare the OpenTime and CloseTimes of all other Trades with the current Trade to see if this Trade opened prior to another closing. As soon as the formula finds another Trade with a CloseTime greater than the OpenTime of this trade, it returns True.
This handles occurrences where you have many trades that happened within another trade’s OpenTime and CloseTime, like you see in the screenshot below. The trades in Row 13 and 14 both happen within the Open and Close time of Row 10’s trade but Row 13’s trade ends before Row 14’s begins. If we just compared to the last trade (ordered by OpenTime), this would have caused a problem - a problem I ran into 
but by iterating over all trades, occurrences like this will still be flagged 
1 Like