How to auto create new record every day?

Nguyen_Nhat_Dong
3 wk ago
29replies
Answered

I have to create a daily record, but I have to hit the button every time. Now I want to use loop function to create new record every day. can someone show me how please?

- Fred
- 3 wk ago
- Reported - view
There isn't a way to run a script based on time.

You can do the create() command when you open the DB, Trigger on open. You can add code that will check if a record for the day already exist and only create a record if there is not a record for the day.
- Nguyen_Nhat_Dong
  
  3 wk ago
  
  Reported - view
  can you please give me a code for example?
- Nguyen_Nhat_Dong
  
  3 wk ago
  
  Reported - view
  sorry I was just newbie with this.
- Fred
  
  3 wk ago
  
  Reported - view
  what is the name of the table and the name of the fields that you want set when a new record is created?
- Nguyen_Nhat_Dong
  
  3 wk ago
  
  Reported - view
  table named : "THU/CHI", name of the field is "CHUYEN HANG". I want to auto create the record and in the field name "CHUYEN HANG", I want it set to the lastest records since "CHUYEN HANG" is the relationship field.
- Fred
- 3 wk ago
- Reported - view
Try creating a button on the Home table with:

create 'THU/CHI'

Now if we add this to the Trigger on open for the DB then it will create a record everytime you open DB and that is not good. So we need to add some logic.

You can then modify the button code to:

let anyOldRecs := select 'THU/CHI' where NGAY = today(); if count(anyOldRecs) < 1 then create 'THU/CHI' end

So if you try it out, it shouldn't create a record since you already did for today. If you delete the record with today's date then click the button it should work.

If all this works then copy the code to the Trigger after open for the your DB.
- Nguyen_Nhat_Dong
- 2 wk ago
- Reported - view
@Fred there is something wrong with this formular. It keep create 2 record, and I have to delete, when it happen every time.

please help me.

let anyOldRecs := select 'THU/CHI' where NGAY = today(); if count(anyOldRecs) < 1 then create 'THU/CHI' end
- Nguyen_Nhat_Dong
- 2 wk ago
- Reported - view
Everytime I trigger it, some time it create 1 record and I try to trigger it many times it is not create another one. But some time, it create another one. I don't know what happen with it.
- Fred
- 2 wk ago
- Reported - view
said:
Everytime I trigger it, some time it create 1 record and I try to trigger it many times it is not create another one. But some time, it create another one. I don't know what happen with it.

said:
there is something wrong with this formular. It keep create 2 record, and I have to delete, when it happen every time.

So which one is it?

next to your button create a new formula field and put this in it:

let anyOldRecs := select 'THU/CHI' where NGAY = today(); concat(anyOldRecs)

Before you press the button see if you see any records in the field. It should only create a record if there are no records found.
- Nguyen_Nhat_Dong
  
  2 wk ago
  
  Reported - view
  I have tried but it still not working. There is the record in the field, when I press a button it create another one. But can you tell me what is the point of creating another formular next to it?
- Mel_Charles
  
  2 wk ago
  
  Reported - view
  Fred is asking you to do this to TEST the logic and make sure it is doing its job
- Nguyen_Nhat_Dong
  
  2 wk ago
  
  Reported - view
  now the code it working, It feel like nothing wrong with the code, but from the time from 6AM to 6 PM it working great, and from 6PM to 6AM the next day it wrong again. I don't know what happen with ninox tho.
- Fred
  
  2 wk ago
  
  Reported - view
  The time of day should not be an issue, but that is why I recommended you create a new formula field next to the button.
  
  Watch the field the next couple of days, do you see data in the formula field before you press the button? You can also test things by deleting the record in 'THU/CHI' that has today's date.
  
  Make a note of when you see data and when you don't.
  
  When you see data then the button should NOT create a record. Make a note if a record gets created when you see data in the field.
  
  These are all steps to learn to how to troubleshoot your code logic. Luckily this is a simple if then so we can quickly test different conditions.
- Nguyen_Nhat_Dong
  
  2 wk ago
  
  Reported - view
  I tried to go to webinar, they said it because of the day time problem. because the day time of the server is different than day time here in San Diego California.
- Nguyen_Nhat_Dong
- 12 days ago
- Reported - view
I tried to do this but it keep Create 2 record. I am so confusing now, because it work well from the morning, and then when it come to 4:00 PM at the afternoon, it create another record. Please help me I don't know what to do now. I tried to test so many time but it didn't workout. I am so desparated.

let anyOldRecs := select 'THU/CHI' where NGAY = today(); if count(anyOldRecs) < 1 then create 'THU/CHI' end
- Fred
  
  12 days ago
  
  Reported - view
  you said you are in San Diego?
  
  are you using the app or cloud? If the app which version?
- Nguyen_Nhat_Dong
  
  12 days ago
  
  Reported - view
  I am using cloud in chrome.
- Fred
  
  12 days ago
  
  Reported - view
  which time zone?
- Nguyen_Nhat_Dong
  
  12 days ago
  
  Reported - view
  my time zone in San Diego is (UTC - 07:00) Arizona. But the Sever is in Berline is (UTC +2) Central European Summer Time. For example, Now is 1 PM afternoon in SD, but in Berline is 10PM. 9 hours ahead of me.
- Fred
  
  12 days ago
  
  Reported - view
  Thanks, I'm going to try it after 4pm PST as I am also in PST.
- Nguyen_Nhat_Dong
  
  12 days ago
  
  Reported - view
  ok thanks, tell me if you try it, thanks you.
- Fred
- 11 days ago
- Reported - view
said:
Thanks, I'm going to try it after 4pm PST as I am also in PST.

Well I was able to duplicate the issue. I thought they fixed date issues like this when they made DBs Timezone independent. What an annoying "feature".

Did Ninox say there was no way to make this work?

Playing around I was able to get it find the "correct" records by stripping out the year, month, and day out of the today() command, turn them into numbers, then use the date() command to put it back together.

It looks something like this;

let xyear := number(year(today())); let xmonth := number(month(today())); let xday := number(day(today())); let anyOldRecs := select 'THU/CHI' where NGAY = date(xyear,xmonth,xday); if count(anyOldRecs) < 1 then create 'THU/CHI' end

This is where used the extra formula field I suggested earlier comes in handy. Originally, I put this in the code box:

debugValueInfo(select Table1 where Date = today())

And it would show me:

nid([])

which means it found no records matching even though there was one.

Then I played around and settled on this:

let t := this; let xyear := number(year(today())); let xmonth := number(month(today())); let xday := number(day(today())); debugValueInfo(select Table1 where Date = date(xyear, xmonth, xday))

and I got back:

nid([A21])

So now I know it found something and now if I transfer the new code to the button then the if statement will work.
- Nguyen_Nhat_Dong
  
  11 days ago
  
  Reported - view
  so there was a bug on today() function right?
- Fred
  
  11 days ago
  
  Reported - view
  I think the bug is in the server not able to convert Date into local time when comparing to today() on the client.
- Nguyen_Nhat_Dong
  
  11 days ago
  
  Reported - view
  so this is the correct way to do this? meanwhile all the today() function in my database have to change.
  
  let xyear := number(year(today())); let xmonth := number(month(today())); let xday := number(day(today())); let anyOldRecs := select 'THU/CHI' where NGAY = date(xyear,xmonth,xday); if count(anyOldRecs) < 1 then create 'THU/CHI' end

Content aside

Status Answered
11 days agoLast active
29Replies
124Views
3 Following

How to auto create new record every day?

29 replies

Content aside

Overview