Relate entities based on dates and make more entities

Get Help
1

I have 2 databases, Planner and Plan Phase. I’m trying to click a button in Planner to auto-generate 1 new entry for each date in the date range of the next unplanned Plan Phase, and associate them with that Plan Phase.

In basic (and likely bad) Psuedocode, I imagine the steps being like so:

  1. When you click the button,
  2. Create a new Planner entity and
  3. Look for the “smallest” [Plan Phase].[Date Range].Start() after the “biggest” Planner.[Target Publication Date] without any Planner entities and
  4. Relate that [Plan Phase] to this Planner entity and
  5. Update the [Target Publication Date] to the [Plan Phase].[Date Range].Start() then
  6. For as many days as [Plan Phase].[Date Range], repeat 2, 4, and update that [Target Publication Date] to [Plan Phase].[Date Range].Start() +1

What am I missing that would make this really simple? I tried scripting but keep getting id errors. Tried using formulas but I keep getting “can’t do it that way”…

2

In other news, this formula works for most of the entries but for some of the entries it doesn’t work.

Places.Filter(
  Date(Year(Begin), Month(Begin), Day(Begin)) <=
    Date(
      Year([This Planner].[Target Publication Date]),
      Month([This Planner].[Target Publication Date]),
      Day([This Planner].[Target Publication Date])
    ) and
    Date(Year(End), Month(End), Day(End)) >=
      Date(
        Year([This Planner].[Target Publication Date]),
        Month([This Planner].[Target Publication Date]),
        Day([This Planner].[Target Publication Date])
      )
)

It seems no matter how I try to specify “just look at the date” any Begin dates with a time of 23:00 or later get ignored.

3

Not even this works.

Places.Filter(
  Day(Begin) <= Day([This Planner].[Target Publication Date]) and
    Day(End) >= Day([This Planner].[Target Publication Date])
)
4

In what timezone are you? I think it’s caused by timezone problems :face_with_peeking_eye:

5

Can you explain what you’re trying to achieve overall (imagining you weren’t tied to Fibery or any other tool).

This may well be a problem:

https://the.fibery.io/@public/User_Guide/Guide/Timezones-41

6

All the times are supposed to be in UTC - suppose I should’ve expected issues letting it detect my time zone for Today() and Now() but I don’t see why something like a formula extracting the date should change the date. It’s maddening.

Date(Year([Peak Date]), Month([Peak Date]), Day([Peak Date]))

Returns +1 Day on anything later than 21:00.

7

If you want the simple explanation for why timezones don’t behave as you would like, it’s this: imagine two users in the same workspace, one in Singapore, and one on New York. What is the result of Today() and Now()?
These users expect different answers, but Fibery doesn’t allow (number/string) formulas to show different values depending on who’s looking. So Fibery chooses to show the same value for both users, based on UTC (which will be wrong for both of them, but wrong in different ways!)

8

Thanks again so much for the time zones tip - I was just about to look for that documentation.

Overall I’m building a content suggestion engine. Micro for this specific query – I’m trying to relate events (let’s call them Apples, Oranges, and Grapes) to time periods and relate each event in the same time periods. So there’s some events that have spans of time that are relevant (Apples), some some events that only have one day (Oranges), and some events that have a date-time (Grapes).

If the date of the date matches, relate them.

9

Whelp, all my data is screwed unless I can set my time zone somehow.

I’m in Denver. I imported a bunch of data in UTC expecting the server to keep the data exactly as I imported it (csv import). Instead, it treated the times as my local time, converted for me being in Denver THEN stored the values.

I saw “all values are stored in UTC” and “the server is not aware of user time zone” and thought it meant “no conversions occur”

10

This doesn’t quite simplify things for me.

Are you saying Fibery does not allow users to set their own time zones? Because I can’t find anywhere to set my time zone.

Or are you saying Fibery does not convert times? This one seems the least likely of the two, given that I have found a different output time value versus the time I know was imported via CSV.

Apart from that confusion… can you help me sort out how to put my data in so that I can get the dates to match? Like, it would be awesome if it could just ignore server time zones and just operate in UTC, like I do, because I’m a digital nomad living anywhere across 5 time zones and working anywhere across 10 time zones.

I see no way to set my time zone to anything so it looks like I’m going to have to delete the databases and start over somehow. But I don’t know how to import it so that the dates match when they’re supposed to.

11

Fibery does not allow users to choose their timezone

Fibery will convert a time (stored in UTC in the backend) into the timezone of the user (based on the browser).

If you want to force Fibery into thinking you are ‘in UTC’ then you can tell your browser to spoof that timezone.

12

Well there’s that for my own environment at least, but I was looking to build a tool that can be handed off (one of the main perks to Fibery for this project is the ease of creating another of the same instance via link).