Update remaining days on leave request

[PedroFernandes]

Hi

I'm building a leave request application using PowerApps for Teams. Here, the users from a Office365Users work group can make their requests and a manager can review and approve or reject them.

Currently I have 2 tables: one (REQUESTS) that records all the requests (with a field [requested_time] that records how much time (in hours) is being requested), and other (USERS_DATA) that keeps record of each user and how much leave time he has left ([remaining_time]).

What I need to know is how to patch the USERS_DATA when the manager accepts a request: it should lookup which user made the request, and subtract the [requested_time] (from REQUESTS table) value to the [remaining_time] (from USERS_DATA table).

The image below shows the manager page with all the pending requests: when the manager clicks on the green button is when the patch must be mad. That change occurs only on the selected request (that's why all pending requests have those buttons).

Any idea how can I achieve this? Please tell me if I need to clarify something.

 

[BCLS776]

It looks like you are working within a gallery, so the Patch should look something like:

Patch(USERS_DATA,
    LookUp(USERS_DATA, User = ThisItem.User),
    {
        'remaining_time': 'remaining_time' - ThisItem.'requested_time'
    }
)

I probably don't have the column names/syntax correct, but let Power Apps help you fill those in correctly.

Hope that helps,

Bryan

[PedroFernandes]

Thank you for your help. I've already tried a similar formula, but it gave me the following errors:

1.

    LookUp(USERS_DATA, User = ThisItem.User),

It says they're incompatible types for comparison: record, text.
2. 

 'remaining_time': 'remaining_time'

The second time "remaining_time" is written, it gives the error that the identifier isn't recognized.

[BCLS776]

The errors you are seeing are due to me not knowing the exact column and control names from your app. Please replace those with the proper ones from your app, keeping in mind these descriptions:

In this statement:

LookUp(USERS_DATA, User = ThisItem.User),

The first use of User is the name of the column in USERS_DATA that contains the name of the users. ThisItem.User refers to the control name in your gallery that contains the name of the user (you probably need to put .Text at the end of it)

In this statement:

'remaining_time': 'remaining_time'

'remaining_time' refers to the name of the column in USERS_DATA that contains the leave time left remaining for the user. Let the Intellisense feature of Power Apps choose this column as you type, so it will include the single quotation marks only if necessary.

[PedroFernandes]

With your help I managed to work on the first error (working with Office365 connector, here). It was something like: 

LookUp(USERS_DATA, User.'Full Name' = ThisItem.User),

As for the second problem, I understand where the 'remaining_time' comes from, but the Intellisense feature isn't recognizing it. I also tried:

'remaining_time': USERS_DATA.'remaining_time' - ThisItem.'requested_time'

Here, the error says it's an invalid argument type (expecting number, text or boolean)

[BCLS776]

You may have to wrap the arguments in a Value() function to ensure they are handled as numbers:

'remaining_time': Value('remaining_time') - Value(ThisItem.'requested_time')

Also 

USERS_DATA.'remaining_time'

Is the name of a column in a table, not a specific reference to a record's value - that would still show an error

[PedroFernandes]

With the Value() funtion, the error says the identifier isn't recognized.

I understand the error, but I can't understand how to reach the exact 'remaining_time' entry for a specific user. I thought the Lookup() function before would do the trick...

[BCLS776 · Accepted solution]

Ah, we need to handle the scope of the filter using With():

With({aLookup: LookUp(USERS_DATA, User = ThisItem.User)},
    Patch(USERS_DATA, aLookup,
    {
        'remaining_time': Value(aLookup.'remaining_time') - Value(ThisItem.'requested_time')
    }
    )
);

[PedroFernandes]

Maybe the answer lies there.... Can you tell me what aLookup refers to? I'm still getting the same error for 'remaining_time'.

[BCLS776]

aLookup (the name I assigned to it) is a very temporary variable that only exists during the execution of the With() statement. It's a very handy piece of code.