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.
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
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.
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
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
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?
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.
So in the Prestaties table I create a new formula field and put this in the formula:
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.
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?
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?