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
    • Ninox partner
    • RoSoft_Steven.1
    • 3 mths ago
    • Reported - view

    Try:

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

    Repeat the conditions for each value....

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

       I've tried the following code but it then only displays the 2nd date and not the 1st

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

    • Ninox partner
    • RoSoft_Steven.1
    • 3 mths ago
    • Reported - view

    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?

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

      I have tried that also but still doesn't work. I can send screenshots of whats going on?

    • Fred
    • 3 mths ago
    • Reported - view

    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.

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

       Sorry for the delay in replying, I've been off this past week. 

      Thank you for taking a look and providing a possible solution, but I have just tried and it still doesn't get it to work unfortunately. Instead of displaying a long list of entries which I usually have using the 1 date ('Fitting Date/Time'), it is only then displaying 1 entry

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

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

    • Fred
    • 3 mths ago
    • Reported - view
      • JamesLIv.1
      • 3 mths ago
      • Reported - view

       how would I go about that? copy database and remove records etc is it?

      Sorry if I don't seem very tech savvy :) I've never done any sort of coding before but just learnt what I could on Ninox to create something that works for me

      • Fred
      • 3 mths ago
      • Reported - view

       are you using the app or the browser? if the browser which level of subscription do you have?

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

      I'm currently still using the app on iMac 

      • Fred
      • 3 mths ago
      • Reported - view

       So yes, duplicate the DB and remove any personal info.

      Go into Admin Mode.

      Click on the DB name in the top left corner.

      You should see a button for Export Data. Click it.

      Then select Ninox from the tabs in the new window.

      Click download, give it a name and click OK.

      Then come back to the forum. Start a Reply and right above the Post/Cancel button is a paper clip 📎 icon. You can do the rest.

      • JamesLIv.1
      • 3 mths ago
      • Reported - view
      • Fred
      • 3 mths ago
      • Reported - view

      so where is this view element?

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

       ah sorry. So the view I'm trying to code is in the Fitting table (underneath Customers) and on the 'Date Search' form.

      When a new customer is created, a fit is scheduled with a date etc, and that View shows me all the appointments booked with a chosen time frame

      • Fred
      • 3 mths ago
      • Reported - view

      can you upload another one that is not locked? can you add some test data?

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

       ninox export (sample DB).ninox

      I've added a couple of customers and created fitting appointments for them. You will see that I have created a 1st and 2nd fitting date for 2 of them, and the view is showing the 1st date

    • Fred
    • 3 mths ago
    • Reported - view
     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”.

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

      ok I think I understand what you mean. I hadn't considered recording it in that way

    • Fred
    • 3 mths ago
    • Reported - view

    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.

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

       I had made a mistake when I initially tried it, I out an "and" instead of "or". 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 created a "Test" customer and put 2 dates in to the fitting form. As you can see on my 1st screen shot with my original code it is showing on there, but after I then input your code it shows me no results at all somehow?

      I understand your logic on using a dashboard instead. I haven't created 1 in a long time now, but I think I will look at creating 1 to use it in that way instead. 

      Many thanks

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

       

    • Fred
    • 3 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
      • 3 mths ago
      • Reported - view

      I tested it again on a local DB and it worked, but only after I noticed that you are using a different view element. I wrote the code for the Fitting Appointment view element on the Fitting Form tab. If you copy it to the view element the Start and End date are using the wrong fields. Check that you are using the correct Start and End date field.

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

       morning Fred, I just tested the code on the database I sent you and it still returns all blank results the same as what I showed above?

Content aside

  • 3 mths agoLast active
  • 30Replies
  • 197Views
  • 4 Following