0

Formulate View field from multiple date entries?

I have a View field to show me fitting appointments between 2 selected dates (start date & end date), where it shows me all appointments where the "Fitting Date/Time" falls in between these:

let xbeg := 'Start Date';
let xend := 'End Date';
select Fitting where 'Fitting Date/Time' >= xbeg and 'Fitting Date/Time' <= xend

This works perfectly, but I wish to add an extra date field on the fitting appointment called "2nd fit date", if the job is being carried out over multiple days. 

Is it possible to have a View where I can incorporate both dates?

i.e 'Fitting Date/Time' and '2nd Fit Date' >= beg?

(p.s. I've tried that and it doesn't work ;D)

30 replies

null
    • Fred
    • 5 mths ago
    • Reported - view
     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?

      • Fred
      • 5 mths ago
      • Reported - view

      I don't know what to say. I have this (thanks to eagle eyed ) in the Date Search tab:

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

      And it works for me.

      Can you upload the modified test DB with your "new" code that still doesn't work?

      • JamesLIv.1
      • 5 mths ago
      • Reported - view

       I was sure that code was going to work then when I put it in, but unfortunately it still only shows the 1st 'Fitting Date/Time' in the results as you can see in the screen shots below. Both Mr Jones and Mr Alec have 2 fit dates selected, but only the 1 is being displayed

       

       

       

      • szormpas
      • 5 mths ago
      • Reported - view

       Hi, here are some comments I hope you'll find helpful:

      • You forgot the "<= xend" at the end of your code above.
      • If you want to see the '2nd Fit Date' then you should add a new column in the view (see the screenshot).
      •  is right. If you want to see in the view element both 'Fitting Date/Time' and '2nd Fit Date' as a separate rows then you have to create a subtable for the Fitting Dates.
      • Alternatively, you can create two different views, one for the 1st Fitting Dates and one for the 2nd Fitting Dates (see the screenshot).
      • Fred
      • 5 mths ago
      • Reported - view

      Just a bit more background. Ninox runs on records. Anytime we do a select or access reference fields we are getting back records. So in the Fitting table we are looking for data across two different fields, 'Fitting Apt.' and '2nd Date', but both fields are in a single record. So Ninox will return each record that satisfies the filter. Thus you only see 1 record for Mr. Jones and Mr. Alec.

      Another limitation of having multiple data fields that store different instances of the same data.

    • szormpas
    • 5 mths ago
    • Reported - view

    Hi   and  , I've uploaded the 'ninox export (sample DB).ninox' into my workspace (cloud) to test.

    I noticed that the 'Fitting Date/Time' is an appointment field and not a 'datetime' one. Based on that I first used the start() function to extract the start timestamp of each appointment and then used the date() function to extract the start day of the appointment.

    I can confirm that the below code inside the function of the view element works well in my settings:

    let xbeg := 'Start Date';
let xend := 'End Date';
select Fitting where date(start('Fitting Date/Time')) >= xbeg and date(start('Fitting Date/Time')) <= xend
Login to reply

Content aside

  • 5 mths agoLast active
  • 30Replies
  • 221Views
  • 4 Following