Lookup / Filer Array?

(0) Share

Report

Posted on by bonnierobin

Hello

Hope someone can help here.

I have a rate card for rentals which looks a bit like this:

Duration of Hire	Number of items	Fee
3	2	100
3	3	200
6	2	300
12	3	400
12	4	500

My customers fill in a MS Forms form using radio buttons where they specify how long they want to hire for and how many items they are hiring.

I want to build a flow that emails them a fee based on those two inputs. I also want to capture that fee and use it elsewhere in the same flow to populate other documents and spreadsheets.

I have my forms set up and my rate card is in a Table in excel. It’s all working fine.

I cannot figure out how to programme the lookup in the Table using two conditions. E.g 6 months, 2 items = 300.

Can anyone suggest a Power Automate function or formula I should use?

Thank you.

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

Verified answer

v-qiaqi@microsoft.com Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!
Hi @bonnierobin,

I did a simple demo for you.

1\ This is my form.

2\ This is my excel table.

3\ This is my flow

1.

item()?['Number of items ']// This is my column name. Your column name may be different from mine.

2.

first(body('Filter_array_2')).Fee

The result is as follows:

Was this reply helpful? Yes No
bonnierobin 10 on at

Like (0)

Report
Copy link

Link copied!

Hello Qi
Thank you for your help.
I'm not getting any outputs and the Initialise Variable is failing.
Please see the attachments.
Really appreciate your help.
B

Was this reply helpful? Yes No
bonnierobin 10 on at

Like (0)

Report
Copy link

Link copied!

@v-qiaqi-msft I got it to work. There was an issue with the array. My fault.
Thank you so much for your help.
All the best.

Was this reply helpful? Yes No
bonnierobin 10 on at

Like (0)

Report
Copy link

Link copied!

Hello @v-qiaqi-msft - wondering if you can help restore this flow for me?

This was working flawlessly up until a month or so ago when it inexplicably began to fail. When I looked at it and tried to rebuild it, PA wanted to Insert ‘Apply to Each’ into the second filter array, but not the first – which left me confused.

The variable cannot be initiated but I am presuming that it because the second filter array is not producing any outputs.

I’ve tried to recreate it from scratch using Apply to Each in both conditions… but…I’m out of my depth. I don’t understand where these have come from and why everything suddenly fell over.

Can you help? Thank you so much.

First Filter Succeeds
Second filter fails
This error: Error:

Unable to process template language expressions in action 'Initialize_variable' inputs at line '1' and column '2328': 'The template language expression 'first(body('Filter_array_2')).Fee' cannot be evaluated because property 'Fee' cannot be selected. Array elements can only be selected using an integer index. Please see https://aka.ms/logicexpressions for usage details.'.

Here are the steps:

Was this reply helpful? Yes No