InfoQube IM - Community

Submitted by LeftEccoForIQ on 2020/06/25 02:22

One thing that I'd find extremely useful but that doesn't seem to be possible out of the box is to quickly filter for items that have recurring events in the calendar. I often have to update my recurring events based on new experiences from the last occurrence and then sort of struggle to find the events quickly if they are on yearly or multi-yearly repeat and I'm not sure of the date.

Comments

Hi Left,

You can try using smart fields for this:

Create a new yes/no field, named ItemIsRepeating, description: Item has at least one event that is repeating
Enter this in the options textbox:
~~<source>SELECT DISTINCT [ItemIsRepeating] AS [Value], -1 AS ItemIsRepeating, ItemID FROM Event WHERE (((recurrence)<>"") AND (ItemID)<>0));</source>~~

[edit] Correct SQL is:
<source>SELECT DISTINCT [ItemIsRepeating] AS [Value], -1 AS [ItemIsRepeating], [ItemID] FROM Event WHERE ((recurrence<>"") AND (ItemID<>0));</source>
[/edit]

HTH !

Pierre_Admin

IQ Designer

Many thanks for your suggestion but: Syntax Error? (or Mental Overflow Error?). Pasting your formula results in a zero item for the ItemIsRepeating field, see screenshot. Do I need to refresh / recalculate this somehow or is there an error in the formula maybe? Thanks!!

~~If you want to include events not linked to items, you can remove the part about ItemID<>0~~

Are all you events coming from Google Calendar ?

This will only list events that are either created in IQ or synced to items

Pierre_Admin

IQ Designer

Tried creating a new item, then dragged it to the calendar and used the event details dialog to give it a yearly repeat, but the item count for the field remained at 0.

I think something is fishy with the formula, though, as I get the following:

- When trying to 'Error reading values of column'
- When trying to create a new grid with ItemIsRepeating as the source: 'Error opening grid. Check grid source criteria.'
- I also once got a popup saying something about a 'syntax error in source'.

As an aside about these error message popups: I find they often disappear when I don't want them to / haven't finished reading them. Is there a way to access an error log where I could re-read such error messages? Or could you (optionally) make these permanent and add a close button?

Figured it out - the final closing bracket in the formula wasn't matching anything. Since removing it, I'm getting an item count. Will now check to see if all the items / events I want are listed. Many thanks, I'm sure this will prove really useful!

Indeed, my mistake. I'll update my original reply with the correct SQL:

<source>SELECT DISTINCT [ItemIsRepeating] AS [Value], -1 AS [ItemIsRepeating], [ItemID] FROM Event WHERE ((recurrence<>"") AND (ItemID<>0));</source>

Pierre_Admin

IQ Designer