0

Use a date to lookup a table to get a code

I've built my database which I use to see progress as  I complete tasks. Thanks for all the help so far!

 

When creating a new data 'page' I seen to have to take a number of steps - too many clicks - and wonder if I can improve this.

 

One of the fields is a 'date code' which I complete my clicking the field which takes me to the 'year' table where I select the year I want. Then I have to do the same with the month. (I want these tables so I can have a list of 'pages' by year.)  I then have a field with the date. That's not a true 'date' field, just text as I don't always have a precise date.

 

So how about if I had a field where I enter the date and that is used to find the year and month codes from those tables and they are filled in in one step rather than five clicks?

 

Possible? If so which general direction should I be looking in? 

 

Here's an example: I enter the date 01 Feb 2000 and the 'date code' field uses the 'year' table to enter AA in the 'year code' field. (But - as now - when I'm in the Year table I see the newly entred 'page' in that list).

 

Hope that makes sence!

11 replies

null
    • Davie
    • 2 yrs ago
    • Reported - view

    So I know how to get the year from a Date field. I need a clue how to use that year and go to my Years table to get its code. 

     

    So in my Years table I have a list like

     

    Year     Code

    2000    AA

    2001    AB

    2003    AC

     

    So when I have the year 2001 I really want AB as my code.

     

    Thanks

    • Fred
    • 2 yrs ago
    • Reported - view

    Hi Davie -

     

    I'm a bit confused on what you want to do. For you second post, something like this will get the appropriate code based on year:

     

    first((select Years [Year = year(Datefield)]).Code)

    • Davie
    • 2 yrs ago
    • Reported - view

    Thanks Fred. Sorry for being unclear. 

    I used your example and this worked for me.

     

    let theYear := year('Date Field');
    first((select Years)[Year = theYear].'Year Code')

     

    Let me try and explain, this may or may not be important.

     

    I'm entering data when I'm in my 'Pages' table. There I have a form I complete. At the moment I have a field on my Pages form which is a reference to the 'Years' and this field displays the Year Code. Currently to enter date there I click it, select a year from the Year Table and now I have the Year Code. This also means the data I am completing in 'Pages' appears when I go to my 'Years' table and look at each year.

     

    So I took your code and modified it a litte and now I have a test field showin the year code based on the date in the date field. But that field is stand alone on the Pages page. Now I really want the code to be the Date Code field which is referenced to the Years table. I've tried your code and variations but can't seem to get it. 

     

    Does the fact I want to enter data in a fiels which is in a different table make a difference?

     

    I'm really sorry, I'm not explaining well! Do ask specific questions if helpful.  Thank you.

    • Fred
    • 2 yrs ago
    • Reported - view

    Let me see if I understand the situation. Looking back at your first post: you have a form view in your Pages table where you enter in data. The form has the following fields:

     

    reference field called 'date code' to Years table

    reference field called ? to Months table

    text field 'date'

     

    You talk about wanting to have Ninox find the Year and Month Code based on the 'date' field.

     

    In your subsequent posts you add a formula field called 'test' that gets the related Year code based on a date field called 'Date Field'). So you have added a date field and called it 'Date Field'.

     

    So the list of fields is now:

     

    reference field called 'date code' to Years table

    reference field called ? to Months table

    text field 'date'

    date field called 'Date Field'

    formula field called 'test'

     

    Then you said, "Now I really want the code to be the Date Code field which is referenced to the Years table."

     

    Question:

     

    1) do you want to be able to enter data into the 'date' text field and have Ninox figure out the Year and Month code?

    If so then

    a) what does the data in the 'date' text field like?

    b) What field in the Years and Month table are you using to look up?

     

    2) do you want to change the test formula field to show data from the 'Date Code' field?

    a) what is the related table's name that has the 'Date Code' field?

    b) I'm guessing that you have a 1:N relationship between a record in Years table and multiple records in the linked table. How do you know which Date Code you want from the many results?

    • Davie
    • 2 yrs ago
    • Reported - view

    Thank you once again Fred. I think you have understood how I have set things up.

    >> 1) do you want to be able to enter data into the 'date' text field and have Ninox figure out the Year and Month code?
    >> If so then
    >> a) what does the data in the 'date' text field like?
    >> b) What field in the Years and Month table are you using to look up?

    The simple answer is no. But maybe. The original 'Date' field is a Text field. I'm just entering the date as "26 Jan 2022" or "Dec 2021" or even when I don't know the actual date just "2020". The long answer is: I assumed I wanted a real date type field otherwise finding the year and month would be harder than with a 'Date' type field. However having a real date type data field limits me to a proper date, I won't be able to enter "00 Feb 2021" or just the month and year.

    >> 2) do you want to change the test formula field to show data from the 'Date Code' field?

    No, this is just a test field for me to learn how to do this. I want my 'Year Code' field - which is a reference field from (or to?) the Years Table to show the year code for the year in the Date field.

    >> a) what is the related table's name that has the 'Date Code' field?

    I have two tables, one for the years and another for months (I wonder now if these could be combined, no matter, let's keep it simple!) The year codes are in the 'Years' table, months in 'Months'. Yes, I want two codes but lets just look at the years and the months will be much the same - I hope!

    >> b) I'm guessing that you have a 1:N relationship between a record in Years table and multiple records in the linked table. How do you know which Date Code you want from the many results?

    Unsure of this question, however: at the moment I create a new record in a 'Pages' view. That view has a field 'Year Code' which is a reference to (or is it from?) the 'Years' table. When I click in that field it opens a table of years and codes. I click the year I need and the formula in 'Show As' "Year Code" puts the code in the field. So I select 2000 and the field shows AA.

    So as I innocently see things, I want to automate my selecting a year from the Years table by having it see the year in the Date field and display that year's code. My test setup with a date field and test results field works fine using

    let theYear := year('Date Field');
    first((select Years)[Year = theYear].'Year Code')

    As I read this for a date in 2000

    theYear is 2000 now go to the Years table and show me 2000's Year Code

    Thanks again.

    • Fred
    • 2 yrs ago
    • Reported - view

    Ok, I think I get it now. From your answers you do want part of #1. You want to enter data in a field and for Ninox to then set data in another field which is a reference field. It is a bit tricker to set a reference field then a text field.

     

    Sounds like you want to continue with the date text field so you can be flexible. May I recommend that you try to stick to a format that always starts with the year, i.e. 2000 or 2001-Jan or 2002-07-23 etc. You will see later why.

     

    Let us start with a button, add the following code (and modify it with appropriate field names) to the On click part:

     

    let d := substring(date, 0, 4);<--this gathers the first four characters of the date text field, see above about always starting with the year
    let getYearID := first((select Years)[year = d]);<--this finds the Id of the record in the Years table that has the matching year
    YearCode := getYearID<--this sets the link in the YearCode reference field in the Pages table to the appropriate record

     

    If that works as expected then you can copy the code into the Trigger after update of the Date text field. Now whenever you update the Date field it will change the link in the YearCode field.

     

    I hope this helps.

    • Davie
    • 2 yrs ago
    • Reported - view

    Thank you once again Fred, yes, really helpful, your code worked for me!

     

    However I don't really want to have to enter the date in Year-Month-Day format . I thoght I'd be clever and see if I could extract the year from the last four characters of the Date text string. I thought I could count four from the end of the string but I couldn't get that to work. Can't I use

     

    let d := substring(date, -4, 4) or maybe let d := substring(date, -3, 4)

     

    But I had no luck so moved to a proper date type date field and used year(DateField) instead.

     

    (I'm slightly disadvantaged by not really knowing the code Ninox is using. It might be Javascript but not everything I try after researching works. So it is but it isn't?)

     

    I learned a lot from your suggestions including buttons! So instead of 'trigger after update' I'm using a button to do the update. This means I can change the day field to "00" and not worry about over writing acidently if for some reason the date changes. Not explaining that well but it works for me!

     

    I used your 'Year' sugestions to do the same for my Month Code. Eventually I worked out that month(DateField) returns not text (Jan, Feb, Mar etc) as I expected but a month number. So I added a Month Number field to my Months table and it works now.

     

    So all good, thank you.

    • Fred
    • 2 yrs ago
    • Reported - view

    Hi Davie -

     

    I don't think I suggested you enter in data typical Y-M-D format. If you look at my comment all I said was to start any data entered with the year.

     

    Regardless, if you want to always put the year at the end of the string then you can add the following:

     

    let c := length(date);<--you can guess what we are doing here
    substring(date, c - 4, c)

     

    Let us know how it goes.

    • John_Halls
    • 2 yrs ago
    • Reported - view

    Hi Davie

    Essentailly what you have here

    Year     Code

    2000    AA

    2001    AB

    2003    AC

    Is a conversion from base 10 to base 26 but offset by some years. Your AA is the equivalent of the 00 part of 100 so we will bulld a function to turn 2000 into BAA and the take the two least significant places. This formula will do that:-

     

    let f := year(Date);
    let a := "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
    let b := "";
    let i := f - 1324;
    while i > 0 do
    let c := i % 26;
    i := floor(i / 26);
    b := substr(a, c, 1) + b
    end
    ;
    substr(b, 1, 2)

     

    Put this in a formula field and replace Date in line 1 with your own date field.

     

    Regards John

    • Davie
    • 2 yrs ago
    • Reported - view

    @ Fred

    >> I don't think I suggested you enter in data typical Y-M-D format.

     

    No, sorry, I was being economical in my reply. I think I got your meaning.

     

    let c := length(date);<--you can guess what we are doing here
    substring(date, c - 4, c)

     

    This is where my little knowlwge comes a dangerous thing. Lets say my string is "Jan 2022". That's eight charactures. So c is 8. The substring uses "Feb 2022", starts from position 4 (8 - 4) and gets the next 8 charactures? Can't be but that's how I've used this type of code in the past. This must be different as we don't seem to start counting at zero here.

     

    Ah, because you use subsrting not substr.

     

    substring(text, start, end) To return a new string out of a given text with a given start and a given end. The start is zero-based.

     

    substr(text, start, length) To return a new string of a given length out of a given text with a given start. The start is zero-based.

     

    Thanks, I'm learning a fair bit here.

    • Davie
    • 2 yrs ago
    • Reported - view

    @ John Halls

     

    Thanks you. Well above my level of understanding. I can _almost_ see what's happenning.

     

    I'm not worthy!

Login to reply

Content aside

  • 2 yrs agoLast active
  • 11Replies
  • 357Views