Formulate View field from multiple date entries?

Try:

'Fitting Date/Time' >= xbeg and '2nd Fit Date' >= xbeg

Repeat the conditions for each value....

maybe try:

('Fitting Date/Time' >= xbeg or '2nd Fit Date' >= xbeg) and  ('Fitting Date/Time' <= xend or '2nd Fit Date' <= xend)

Can you provide a sample database?

Try these two things:

1) put the date/time fields inside date() commands. So 'Fitting Date/Time' would be date('Fitting Date/Time). I don't know if 2nd Fit Date is also a date/time field, if it is then also put it inside a date() command. If not then leave it out.

2) organize your filters into 1st and 2nd fittings

It would look something like:

select Fitting
    where date('Fitting Date/Time') >= xbeg and date('Fitting Date/Time') <= xend and
    (date('2nd Fit Date') >= xbeg and date('2nd Fit Date') <= xend

I was wondering how Ninox would do with comparing date/time to date and it looks like it is kinda wonky once you add the second filter. You should always make sure data is similar so you should convert the date/time data to just date if you are only comparing dates.

Just food for thought, I don't know how many jobs go on for multiple days, but you may want to consider a different DB design. Many think it is easy to just add another field called 2nd or field2 to store the same type of data but just another instance. But it makes it more complicated to filter. See this post for an example.

What if you have the odd job that is three days? Do you add a third date/time field? Now you have to modify all of your formulas/views to take into account this third field. Or your business expands and now you have different workers going out on different fit dates?

The view code would change to:

select 'Fitting Schedule' where date('Fitting Date/Time') >= xbeg and date('Fitting Date/Time') <= xend

Just keep it in mind.

James said:
but I have just tried and it still doesn't get it to work unfortunately

Can you provide a sample DB?

James said:
what would you mean by a different db design as such?

Many users, myself as the leader, fall into incorrect DB design when they are starting out. You come across the need to store two different instances of the same type of data. In your case you have two date/time fields that you want to track for each Fitting record. So you created a second date/time field called 2nd Fit Date.

Now, what will do if a new job needed a 3rd Fit Date? Do you create a third date/time field? Or you want to track who goes out to do the fit on each day? Now do you create 3 more fields called Fit Date Worker 1, Fit Date Worker 2, etc.

The correct DB design when you start encountering the need to track multiple instances of the same data is to create a child, sub, table. 

In your case we can call this new table FittingSchedule. It can have just one date/time field for now besides the reference field to Fittings, with Composition set to Yes.

Now you can create as few or as many fitting dates for each Fitting record as you want. If most of your jobs is only 1 then that is cool. If suddenly you now are doing two days for each job, that is also cool. And the 1 job a year that now takes 4 days can be added without affecting your structure.

You can now also store information about each fitting date as well. You can add a field that tracks who did the work. You can track a different location for each day. You can mark a date as a “Special Order” so you can charge more, or mark it as “Discount”.

Well I tried this:

let xbeg := 'Start.Date';
let xend := 'End.Date';
select Fitting
    where date('Fitting Date/Time') >= xbeg and date('Fitting Date/Time') <= xend or
    date('2nd Fit Date') >= xbeg and date('2nd Fit Date') <= xend

and it works.

With a start date of 7/11/24 <- US format and end date of 7/25/24, I get three records. If I change the start to 7/17/24 then I see only two records.

You also encounter the DB design issue when you use multiple data fields. In your view element you show Fitting Date/Time, but what about 2nd Fitting Date? Now you have to always show it, so you can see the data, but you don't always need it.

Something else to ponder, when you are ready look into Dashboards. It may seem like more work, but in the end it saves you time and steps. A dashboard is a single record in a table that you use as a user interface to your data tables. The idea is separate the data tables from direct interaction from the user.

You have basically created a Fitting dashboard, the Fitting Form view, but it shouldn't be in the Fitting table. Why? The Fitting table is where you store data for each fitting. You have randomly selected record 188 to be the Form View. What if you need to delete record 188, or it gets deleted?

Anyways, not something you need to do now, but something to keep in mind.

James said:
However I just copied your code and put it in but it still doesn't work. I've just taken 2 screen shots to show you. 

I don’t know what to say, but it works for me. Can you try it in the same DB you sent me?