0

List field content using rsort()

I'm trying to print a list of all entries in a given field between two specified dates.

With the precious  help of other posts in this forum I managed to get the list but it still brings up all the records dates and not only the ones with entries on the specified field…

I'll really appreciate any help.

let myStartDate := 'Date start';
let myEndDate := 'Date end';
if 'Folha de Ponto'['Data FL_CX'.Data >= date(myStartDate) and 'Data FL_CX'.Data <= date(myEndDate)].'Observações' != null then
    join(rsort(Table1['Data FL_CX'.Data >= date(myStartDate) and 'Data FL_CX'.Data <= date(myEndDate)].('Data FL_CX'.Data + " " + 'Observações')), "
")
else
    if 'Folha de Ponto'['Data FL_CX'.Data >= date(myStartDate) and 'Data FL_CX'.Data <= date(myEndDate)].'Observações' = null then
        null
    end
end

10 replies

null
    • Fred
    • 1 yr ago
    • Reported - view

    Just to clarify a few points:

    1) is the field 'Data FL_CX'.Data a date field? if not then what type of field is it?

    2) are 'Date start' and 'Date end' date fields? If not then what type of fields are they?

      • fuchsia_cactus
      • 1 yr ago
      • Reported - view

      Fred Hello Fred,

      1) Yes 'Data FL_CX'.Data is  a date field.

      2) 'Date start' and 'Date end' as well.

    • Fred
    • 1 yr ago
    • Reported - view

    If you put the following code in a formula field do you get the correct record Ids? 

    'Folha de Ponto'['Data FL_CX'.Data >= myStartDate and 'Data FL_CX'.Data <= myEndDate]

    Replace or create a new formula field and put the following code in to check it: 

    Table1['Data FL_CX'.Data >= myStartDate and 'Data FL_CX'.Data <= myEndDate]

    Does this return the correct record Ids as well?

      • fuchsia_cactus
      • 1 yr ago
      • Reported - view

      Fred The first line doesn't get anything, the second one actually brings up record Ids, although not the pretended ones…

      What I'm trying to achieve is to get only the dates corresponding to the days where entries in the field 'Observações' had been made…

      • fuchsia_cactus
      • 1 yr ago
      • Reported - view

      Fred I have to correct my previous answer to your question, the record Ids are in fact correct, they just appear in a different order, sorry!…

    • Fred
    • 1 yr ago
    • Reported - view

    Glad to know that the searches are correct.

    VM said:
    What I'm trying to achieve is to get only the dates corresponding to the days where entries in the field 'Observações' had been made…

     I'm not sure I understand what you want. Do you want to find records where 'Observações' is not null? Or do you want to find records between particular dates?

      • fuchsia_cactus
      • 1 yr ago
      • Reported - view

      Fred Thank you for your patience!! :-)

      I forgot to tell that this code is to be used in a field on the print dialog.

      So, every month end I print all the hours spent by somebody in a given project.

      What I'm trying to achieve is that the field 'Observações:' on the print layout collects all the inputs made in a given field ('Observações') of a table ( 'Folha de ponto') inserting before the observation the date of the corresponding record.

      I actually managed to bring up these information, problem is that I'm getting all the dates in the given time frame even those without any entries in the targeted field ('Folha de Ponto'.'Observações').

       

      Resulting in a long list of dates only. With a funny organization btw…

       

      Thank you!!

      • fuchsia_cactus
      • 1 yr ago
      • Reported - view

      VM 

    • John_Halls
    • 1 yr ago
    • Reported - view

    Try using Fred's test code in a view 

    'Folha de Ponto'['Data FL_CX'.Data >= myStartDate and 'Data FL_CX'.Data <= myEndDate]

     

    This should bring up a list of records which you can then adjust to just show the fields you want to see.

    • Fred
    • 1 yr ago
    • Reported - view
    VM said:
    What I'm trying to achieve is that the field 'Observações:' on the print layout collects all the inputs made in a given field ('Observações') of a table ( 'Folha de ponto')

     Ok, so if I understand you correctly. The first thing you want to search on is if 'Observações' empty or not. So that would something like: 

    let step1 := 'Folha de Ponto'[Observações != null]

    We will put the results in a variable so we can then filter the returned results. It is also important to note that what we are doing here is finding the records that match the search criteria. We have not limited ourselves to just a single field. As you will see in the next bit of code we can now further filter the found set in step1. 

    let myStartDate := 'Date start';
let myEndDate := 'Date end';
let step1 := 'Folha de Ponto'[Observações != null]
let step2 := step1['Data FL_CX'.Data >= myStartDate and 'Data FL_CX'.Data <= myEndDate]

    You can see here we have added in some bits from your original code and now in step2 we have filtered out the records that don't match your requirements by date.

    You said:

    Resulting in a long list of dates only. With a funny organization btw…

    By default Ninox sorts by record Id. If you go to the table 'Folha de Ponto' and in a table view show the record Id and field 'Observações', or whatever is the appropriate field, you will see a similar sort order.

    So to fix this we can do the following: 

    let myStartDate := 'Date start';
let myEndDate := 'Date end';
let step1 := 'Folha de Ponto'[Observações != null]
let step2 := (step1['Data FL_CX'.Data >= myStartDate and 'Data FL_CX'.Data <= myEndDate] order by Observações)

    I don't remember if Observações is a date field or not, but you can change it to the appropriate field name.

Login to reply

Content aside

  • 1 yr agoLast active
  • 10Replies
  • 127Views
  • 3 Following