Linktofilteredview

[Msant77s]

Hello, good afternoon. I'm having a problem with an expression to get to a filtered view. Linktofilteredview()

I have a tool management application. Which has four main tables.

1. Record of delivery of tools.
2. Record of tool returns.
3. Loans.
4. Returns

1. Record of delivery of tools.

In the tool delivery registration table, the only thing that is done is to generate a row with the name of the person to whom the tools are going to be delivered.

Then, through a button and actions, what I do is generate tool loans (table 3) that are later seen within the record as deliveries related to that record and person. Additionally, each time a tool is added to the list, a status is changed from “Available” to “Loaned”.

1. Record of tool returns.

For the tool return record, the operating mechanism is the same as the delivery mechanism, only that to return the related tools to a specific person, a button was used where I have this expression:

LINKTOFILTEREDVIEW(“Loaned”,

   in([Related Loans],

      select(Loans[ID Loans],

         [File or DNI]=[_THISROW].[File or DNI]

    )

  )

).

Everything works perfectly, except that when there is more than one loan associated with that same tool in the past, the filtered view fails. I can't understand why, but I see that the reason is this.

In this image you can see that this tool has two related loans. One to the person "Abdala, Ramon Abraham", who has already been returned. And then it has another related loan that is the current one for which the status of the tool is as loaned. Brino, Daniel Alexander. In the operation of the application when I want to generate the tool return record of "Brino, Daniel Alejando" it should bring me the list of tools that are loaned to him as a collaborator.

 When pressing the button that should take me to the filtered list of tools that are loaned to "Brino, Daniel Alejandro" the system fails when the tools had a previous management.

1. Record of delivery of tools.

1. Record of tool returns.

1. Loans.

1. Returns

[WillowMobileSys]

The reason is this part

 ...in([Related Loans],...

[Related Loans] is a list and the expression is trying to match that list in the SELECT() portion.  The only way there would be a match is if the [Related Loans] list appeared exactly as is - order and all - within the returned values of the SELECT().  It's unlikely to happen and not what you want.

I don't know if your intention is to match ANY of the [Related Loans] in the SELECT() or match all of them.  I'll give both ways below. In either case you will probably want to use the INTERSECT() function.

To match ANY, it is enough to know that there is an overlap of items between the 2 lists:

LINKTOFILTEREDVIEW(“Loaned”,
   INTERSECT([Related Loans],
        select(Loans[ID Loans],
              [File or DNI]=[_THISROW].[File or DNI])
   ) > 0
) 

To match ALL, you need to know there is more than zero AND that the counts of Related Loans  = count of the Intersection:

LINKTOFILTEREDVIEW(“Loaned”,
   AND(
       COUNT([Related Loans]) > 0,
       COUNT([Related Loans]) 
          = COUNT(INTERSECT([Related Loans],
                     select(Loans[ID Loans],
                            [File or DNI]=[_THISROW].[File or DNI])
                  )
            )
   )
) 

 NOTE:  This has not been tested and may not by syntactically correct.

[Msant77s]

Thank you very much for your reply!

1. This expression works but it doesn't give me the list of tools that are on loan to this person.

LINKTOFILTEREDVIEW(“Prestadas”,

   AND(

       COUNT([Related Prestamos]) > 0,

       COUNT([Related Prestamos])

          = COUNT(INTERSECT([Related Prestamos],

                     select(Prestamos[ID Prestamos],

                           [Legajo o DNI]=[_THISROW].[Legajo o DNI])

                  )

            )

   )

)

1. This expression does not work.

[WillowMobileSys · Accepted solution]

The INTERSECT() function should have been wrapped in a COUNT()..like this:

LINKTOFILTEREDVIEW(“Loaned”,
   COUNT(INTERSECT([Related Loans],
           select(Loans[ID Loans],
                 [File or DNI]=[_THISROW].[File or DNI])
        )
   ) > 0
) 

[Msant77s]

Great!! It worked😁
Thank you very much for your time!
I have another query to ask. It is in relation to actions.
For the return of the tools, I have a button that what it does is a set of actions... an action is the one we have already seen in this post, send to a filtered view with the tools that have the "Loaned" status and that correspond to the person of record. And the other action what it does is to update a count of a column of the record row corresponding to that person.

Then inside the view of the borrowed tools I have a button that what it does is send all the data of this tool to the returns table.

The problem I have is that many times this update that I do with the first button of the tool return record does not update the row correctly so that the data falls with maxrow.

What solution could there be for this inconvenience?... can something be added that delays the entry of tools into the registry? for this tool to drop into the correct registry? ie the updated one?