0

Need to show only the Deliverables that will be issue in the next 7 Days.

Need to show only the Deliverables that will be issue in the next 7 Days, from the DataDate.

I need the script that give me information, the information must be taken from the dates FechaP1 to Fecha P5.

All information is on Documetos table. The Documenst table maybe have a lot of Deliverables.

Thanks

19 replies

null
    • Fred
    • 1 yr ago
    • Reported - view
    Rafael said:
    Need to show only the Deliverables that will be issue in the next 7 Days, from the DataDate.

     Help me understand what you want a bit better. So you want to create a formula that checks each Fecha field and if FechaP1 has a date that is within 7 days of the date in DataDate, return a value that you can sort on? Then checks if FechaP2 and so on?

    You want to be able to filter a table view or use it to create a view element in a dashboard?

    • Rafael Sanchis
    • Rafael_Sanchis
    • 1 yr ago
    • Reported - view

    The first point Yes.

    The second point I will like to view in a table view. 

    I need these information for report every week.

    Thank Fred.

    • Fred
    • 1 yr ago
    • Reported - view

    Try creating five new formula fields (one for each Fecha) and put this in the formula:

    days(FechaPx,DataDate)
    

    You will see numbers from positive (days before the DataDate) to negative (days after the DataDate). Do you want to see records where the days are between 0 and 7?

      • Rafael Sanchis
      • Rafael_Sanchis
      • 1 yr ago
      • Reported - view

      Fred 

      Quieres ver registros donde los días están entre 0 y 7? Yes but no weekend

    • Fred
    • 1 yr ago
    • Reported - view

    Here is something quick off the top of my head:

    let dcheck1 := workdays(Date1, DataDate);
    let dcheck2 := workdays(Date2, DataDate);
    let dcheck3 := workdays(Date3, DataDate);
    let dcheck4 := workdays(Date4, DataDate);
    let dcheck5 := workdays(Date5, DataDate);
    let SevenOrLess1 := if dcheck1 > 0 and dcheck1 < 8 then
            1
        end;
    let SevenOrLess2 := if dcheck2 > 0 and dcheck2 < 8 then
            2
        end;
    let SevenOrLess3 := if dcheck3 > 0 and dcheck3 < 8 then
            3
        end;
    let SevenOrLess4 := if dcheck4 > 0 and dcheck4 < 8 then
            4
        end;
    let SevenOrLess5 := if dcheck5 > 0 and dcheck5 < 8 then
            5
        end;
    let array1 := [SevenOrLess1, SevenOrLess2, SevenOrLess3, SevenOrLess4, SevenOrLess5];
    if count(array1) > 0 then 1 end
    

    Then you can filter for 1 and it will show only records that has a DataDate that is 7 working days from any of the Fecha dates.

    I created an array just in case you want to find out which Fecha field has the date that is close to the DataDate.

    • Rafael Sanchis
    • Rafael_Sanchis
    • 1 yr ago
    • Reported - view

    Fred Hi Fred

    After researching on the forum I found a script that I have adapted a bit but I have two problems.

    The first is that this script is up to today's date and I need to adapt it to the DataDate date.

    The second is that it is with a single date FechaPx.

     I really don't know if it's possible to set it aside for my needs

    let rangeDat := for i in range(0, 7) do
            format(date(year(today()), month(today()), day(today()) + i), "DD.MM.YY")
        end;
    let result := [""];
    for i in rangeDat do
        for j in (select DOCUMENTOS) order by 'FechaP5 (Editar)' do
            if i = format(j.'FechaP5 (Editar)', "DD.MM.YY") then
                result := array(result, [j.Documento + if format(j.'FechaP5 (Editar)', "DD.MM.YY") = format(today(), "DD.MM.YY") then
                            " - "
                        else
                            " Date to be issued : / " + format(j.'FechaP5 (Editar)', "DD.MM.YY")
                        end])
            end
        end
    end;
    join(result[!= ""], "
    ")
    
    • Fred
    • 1 yr ago
    • Reported - view

    So it looks like you are trying to use a view element in another table.

    If you create the formula field and put my code, then you can just search on my field for the view formula:

    select Documentos [newfield = 1]

    And this will show you all records that have FechaPx that is 7 days before the DataDate.

    The code does not include records that have Fecha dates that are equal to the DataDate. If you want to include those then change > 0 to >= 0 in the formula field.

      • Rafael Sanchis
      • Rafael_Sanchis
      • 1 yr ago
      • Reported - view

      Fred 

      Fred I create  new table Next7days 

      Where copy your Formula ? and where the select Documentos [newfield = 1]

      I'lost now, sorry 😔

    • Fred
    • 1 yr ago
    • Reported - view
    Rafael said:
    create  new table Next7days 
    Where copy your Formula ? and where the select Documentos [newfield = 1]
    I'lost now, sorry 

     I guess I made too many assumptions.  What are you trying to do in the Next7days table? Please be specific with field names and and any formulas.

      • Rafael Sanchis
      • Rafael_Sanchis
      • 1 yr ago
      • Reported - view

      Fred 

      On DOCUMENTS Table in Tab Issue I copy you formula and give some 1, OK how search the Documents with 1 ?

      On the Next7Day I try other formula you can see that 

    • Fred
    • 1 yr ago
    • Reported - view

    Do you need the data from Documentos to be a text field in your Next7days table?

    Can you add a view element in Next7days and use that?

      • Rafael Sanchis
      • Rafael_Sanchis
      • 1 yr ago
      • Reported - view

      Fred For both Question is YES

    • Fred
    • 1 yr ago
    • Reported - view
    Rafael said:
    For both Question is YES

     Well you made me think about a solution for the formula field part. I've attached a DB with my changes. It is, of course, very rough so you will have to make it pretty like the rest of your DB.

    Some main points:

    In the Documentos table

    1) I modified the Next formula as I discovered that workdays() always returns a positive number regardless if Datadate is before or after the Fecha date. So I had to check for that and make any results where the Fecha date is after the Datadate to show up as negative.

    2) I added a field called fechafields that takes the first part of the Next field and stores the array to be used later.

    In the Next7days table:

    here is the code for the formula field to show the results as text:

    let selDocs := (select DOCUMENTOS)[Next = 1];
    let predisplay := for loop1 in selDocs do
            let xFecha := max(loop1.fechafields);
            switch xFecha do
            case 1:
                loop1.Documento + " - " + loop1.FechaP1 + "
    "
            case 2:
                loop1.Documento + " - " + loop1.FechaP2 + "
    "
            case 3:
                loop1.Documento + " - " + loop1.FechaP3 + "
    "
            case 4:
                loop1.Documento + " - " + loop1.FechaP4 + "
    "
            case 5:
                loop1.Documento + " - " + loop1.FechaP5 + "
    "
            end
        end;
    let display := replace(text(predisplay), ",", "");
    display
    

    In line 3, I can use the max function since fechafields is an array. This is just in case there are two or more fecha dates that are within 7 days and I just decided on the last one. If that doesn't fit your scenario then you can use first.

    Line 22 is needed because a comma separated list is created in redisplay so I used the replace command to replace the "," with a blank space "". As you can see I have a carriage return in the case statements.

      • Rafael Sanchis
      • Rafael_Sanchis
      • 1 yr ago
      • Reported - view

      Fred Thanks a lot Fred 

    • Fred
    • 1 yr ago
    • Reported - view

    The max function seemed limiting to me so I added a loop to allow for multiple Fecha dates to be valid and will show the dates for each valid date.

    let selDocs := (select DOCUMENTOS)[Next = 1];
    let predisplay := for loop1 in selDocs do
            for loop2 in loop1.fechafields do
                switch loop2 do
                case 1:
                    loop1.Documento + " - " + loop1.FechaP1 + "
    "
                case 2:
                    loop1.Documento + " - " + loop1.FechaP2 + "
    "
                case 3:
                    loop1.Documento + " - " + loop1.FechaP3 + "
    "
                case 4:
                    loop1.Documento + " - " + loop1.FechaP4 + "
    "
                case 5:
                    loop1.Documento + " - " + loop1.FechaP5 + "
    "
                end
            end
        end;
    let display := replace(text(predisplay), ",", "");
    display
    

    There is a new line 3 which will now loop through the fechafields field from Documentos and create a line for each Fecha date that is within 7 days of your DataDate.

    It seems like your dates are always more than 7 days apart, but now if they aren't you will know all dates.

      • Rafael Sanchis
      • Rafael_Sanchis
      • 1 yr ago
      • Reported - view

      Fred 

      Hi Fred 

      Example the Document D00 have the following FechaP1 to FechaP5 The DataDate is 02/12/2022

      7/11/2022  11/11/2022  18/11/2022. 25/11/2022. 07/12/2022

      For the FechaP5 the result should be 2 days no -4

    • Fred
    • 1 yr ago
    • Reported - view
    Rafael said:
    Example the Document D00 have the following FechaP1 to FechaP5 The DataDate is 02/12/2022
    7/11/2022  11/11/2022  18/11/2022. 25/11/2022. 07/12/2022
    For the FechaP5 the result should be 2 days no -4

     I'm using the workdays() function and I'm making anything with a Fecha date that is greater than the DataDate a negative, so since 02/12/2022 is a friday, 05/12/2022 is -2 days. So 07/12/2022 is -4 days. workdays() counts the same day as 1 there is no 0. Which I guess makes some sense as that is a workday.

      • Rafael Sanchis
      • Rafael_Sanchis
      • 1 yr ago
      • Reported - view

      Fred Thank you for your effort 👍

      • Rafael Sanchis
      • Rafael_Sanchis
      • 1 yr ago
      • Reported - view

      Fred 

Content aside

  • Status Answered
  • 1 yr agoLast active
  • 19Replies
  • 190Views
  • 2 Following