0

Code the top search - Help

Been working on my DB and thanks to everyone who has been helping me.

I think i am down to the last part that needs to be done and i can start using it.

This is a top of dashboard search as seen in this video.

https://www.youtube.com/watch?v=nUqGl84gh08&t=1334s

I have it all in place but do not understand how to make it all work togather for my DB.

Under the green search results should not show unless a search is run in the top start and end date or Customer search or Date search.

I am including the DB - it is all test info in it.

Also i would like to get all the numbers to show $ for them on the dashboard page.

Here is some of the code i copied but this is just part of it from one area in the video and this is not 100% matching for my use. I was going to change inventory to date to search by a date we know or phone one would be good..

et xBeg := 'BEGIN DATE':

let xEnd := 'END DATE';

let xCst := customer;

let xInv := INVENTORY;

SELECT 'Customers' where Invoice. 'Invoice Date' >= xBeg and Invoice. ' Invoice Date' <= xEnd

Thanks

2022 Biz Database Bata4.ninox 

21 replies

null
    • Fred
    • 2 yrs ago
    • Reported - view

    After reviewing the DB, I noticed that you made the top row of fields view elements. You need to make them fields so you can enter in data.

    I've uploaded a modified DB.

    You will see that Start and End Date are fields and Customer search is now a reference field to Customer table. You could make it a Dynamic Choice field, but there are two reasons I did a reference field:

    1) dynamic choice fields have a limit to how many they can show, and we want to make sure we have access to all of your customers.

    2) you can search on reference fields. you can see I've modified the columns to show names and phone number. you can start typing in a phone number and it will start to filter out numbers that don't match.

    Then I've added a bit of "intelligence" to the Search View view element to see if you are searching for:

    1) either dates or Customer. It is not setup to do both.

    2) if you are searching on dates then it will see if you are searching for only a start or end date or both.

    2022 Biz Database Bata4-1.ninox
    • Edward.1
    • 2 yrs ago
    • Reported - view

    Ok fantastic.. Fred.

    Its all working. Looking at the code for that i would have never got it. Looking at it makes sence but i would not have got all that.

    Thanks very much.

    Playing with it i broke it at one point but going out of the DB and coming back in its back working.

    Any way to hide the Formula field next to the CustomerSearch.. Just dont want my wife who will be doing more of the looking up to get confused.

    Still working on adding the $ in front of the numbers. That side ways M is messing her up seeing it like that..lol

    I sent you a PM

      • Fred
      • 2 yrs ago
      • Reported - view

      You can just delete that formula field.

    • Edward.1
    • 2 yrs ago
    • Reported - view

    Here is the next beta with the e-mail feature added in as far as i was able to go with it.
    I got the code and layout from copying it from a e-mail template in Webinar templates 0_E-Mail but wanted the e-mail templaes feature added in.
    2022 Biz Database Bata5.ninox

    • Jarno_Vande_Walle
    • 2 yrs ago
    • Reported - view

    Hi everybody,

    I've used this code too in my dashboard but there is something peculiar. In my table I have entries on August 13 2022 - some were entered directly in Ninox, others were pulled in from Cognito Forms. When I enter this specific date in the Begin Date and in the End Date in my Dashboard, I don't get all the results - I only get the results that were entered directly in Ninox and not the results that were pulled in from the Cognito form via Make.

    Here is what is really strange: if I put August 12 2022 as the Begin Date and August 14 2022 as the End date; I do get all the results from August 13 2022, including the ones that were pulled in. Since I've used the code "where Datum >= xBeg and Datum <= xEnd" I didn't expect that I would not get the results from the specific date. Any ideas as to what the problem could be?

      • Fred
      • 2 yrs ago
      • Reported - view

      Jarno Vande Walle can you post a sample of your db?

    • Jarno_Vande_Walle
    • 2 yrs ago
    • Reported - view

    Hi Fred

     

    Here is a print-screen of the formula; the rectangle indicates the relevant parameters for this view; the others are for different views. 'Gepresteerde uren' is the name of the view.

      • Fred
      • 2 yrs ago
      • Reported - view

      Jarno Vande Walle Thanks for the picture, but since you are asking for troubleshooting on searching, I find it hard to do without looking at the data.

      Can you make a dummy DB?

    • Jarno_Vande_Walle
    • 2 yrs ago
    • Reported - view

    Hi Fred; sorry I did not understand your question to deliver a sample. What exactly would you want me to do?

      • Fred
      • 2 yrs ago
      • Reported - view

      Jarno Vande Walle Can you upload a copy of your DB with sample data so I can see what is going on?

    • Jarno_Vande_Walle
    • 2 yrs ago
    • Reported - view

    Yes, enclosed you can find the file. For privacy reasons I made a copy and deleted much of the non-essential info as well as the tables that aren't linked.  Kind regards,

    Test-2.ninox
      • Fred
      • 2 yrs ago
      • Reported - view

      Jarno Vande Walle The DB is locked. Please upload an unlocked one.

      Also you only have data for August 12. You were having issues with August 13. Please make sure there is enough data to test the search.

      How can you tell if data was input directly in Ninox and which was done through the API?

    • Jarno_Vande_Walle
    • 2 yrs ago
    • Reported - view

    Hi again, entries with the ID 1 to 12 were pulled in from CognitoForms, ID 13 was not.

    Test-4.ninox Test-4.ninox
    • Fred
    • 2 yrs ago
    • Reported - view

    It is a weird one.

    So when I set the BeginDatum and EndDatum to Aug 12 2022 it shows me nothing.

    The DB you uploaded does not have a record with the ID of 13 in the Prestaties table. But don't worry.

    I then went in to the Prestaties table and manually reset record ID 1Datum to Aug 12 2022. Then it showed up in the KPI table.

    Weird.

    So in the Prestaties table I create a new formula field and put this in the formula:

    raw(Datum)

    What this does is tells Ninox to show me the milliseconds since UNIX epoch for the data in Datum

    Then I put the field in the table view and I get the following:

    The date from the API is about 100M miliseconds off from my manual setting of the date.

    Doing some research, 1660348800000 is actually GMT Aug 13 2022 12:00 am. 1660287600000 is GMT Aug 12 2022 7:00 am. So I guess Ninox is taking our current timezone and converting the above to Aug 12 2022 to display the "correct" date to our eyes, but to the DB it is Aug 13.

    So you will have to investigate your other end and see if it can send back the correct date format you want.

    • Ninox partner
    • RoSoft_Steven.1
    • 2 yrs ago
    • Reported - view

    Jarno Vande Walle

    Try this formula:

    let xBeg := Begindatum;
let xEnd := Einddatum;
select Prestaties where date(year(Datum),month(Datum),day(Datum)) >= xBeg and date(year(Datum),month(Datum),day(Datum)) <= xEnd

    This strips out any differences in the Unix representation of the date.

    Steven

    • Jarno_Vande_Walle
    • 2 yrs ago
    • Reported - view

    Thank you Steven, but unfortunately that does not yield any results. If I change the code that you provided to what follows; I do get the result that was put into Ninox, but still not the ones that were pulled in via Make:

    select Prestaties where Datum >= xBeg and date(year(Datum),month(Datum),day(Datum)) <= xEnd

    I don't know if this helps to narrow the problem?

      • Ninox partner
      • RoSoft_Steven.1
      • 2 yrs ago
      • Reported - view

      Jarno Vande Walle 

      I see in your code the first 'Datum' is not converted with the date function, maybe that's the reason.

    • Jarno_Vande_Walle
    • 2 yrs ago
    • Reported - view

    I tried your formula first but that did not yield any result. That is why I changed it.

      • Fred
      • 2 yrs ago
      • Reported - view

      Jarno Vande Walle In your Prestaties table create a new formula field, you can call it "schonedatum", and put this in it:

      date(year(Datum), month(Datum), day(Datum))

      Does it return it a date?

      If it does then Steven's code should work. It works in my DB.

      select Prestaties where date(year(Datum), month(Datum), day(Datum)) >= xBeg and date(year(Datum), month(Datum), day(Datum)) <= xEnd

      You can try modifying your view code to:

      select Prestaties where schonedatum >= xBeg and schonedatum <= xEnd
    • Jarno_Vande_Walle
    • 2 yrs ago
    • Reported - view

    Fred I tried this too but it also doesn't work. I did found out something. When converting the date to a number, I noticed that the number that was entered directly into Ninox differs from the number that was pulled in through Make. See images below. I then tried to match my query to these numbers (since the number of 'schonedatum' is equal to the number from 'Begindatum' and 'Einddatum'. But this also does not yield any results.

    The difference in time zones might results from the fact the form was filled in by someone who's mobile phone is from Poland? Does this make sense?

    • Fred
    • 2 yrs ago
    • Reported - view

    Have you seen this?

    Is Time-Zone independent turn on for your DB?

    Did you see my questions in my post below?

    Steven's code does work, for me, so I'm wondering what happens on your end.

Login to reply

Content aside

Categories

View all topics
  • 2 yrs agoLast active
  • 21Replies
  • 197Views
  • 5 Following