cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dlprentice
Responsive Resident
Responsive Resident

Rather than doing a bunch of LookUp's in AddColumns is there a way to add all columns from one LookUp?

Basically the title, I am wanting to pull in all columns from a datasource using one lookup via the AddColumns command. Currently I have each and every column added, but as an individual lookup. Feels.... inefficient.

 

1.png

1 ACCEPTED SOLUTION

Accepted Solutions

I realize the LookUp was not being closed. I changed this:

            With(LookUp('Networking Device List Master', 'DNS Entity Name' = _item.cj7y,

 to this:

            With(LookUp('Networking Device List Master', 'DNS Entity Name' = _item.cj7y),

 

View solution in original post

11 REPLIES 11
RandyHayes
Super User
Super User

@dlprentice 

Please consider changing your Formula to the following:

SortByColumns(
    Filter(
        ForAll('ESN Ticket Tracker' As _item,
            With(LookUp('Networking Device List Master', 'DNS Entity Name' = _item.cj7y),
                Patch(_item,
                    {City: City,
                     State: State,
                     'Local Contact Name': 'Local Contact Name'
                     'Entity Hostname': 'Entity Hostname,
                     ...etc...your formula picture cut off here

 

I hope this is helpful for you. 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

@RandyHayes 

I think this is what I'm going for, but did you mean to use Patch? I am just trying to display data from 2 tables.

RandyHayes
Super User
Super User

@dlprentice 

Yes, absolutely - Patch puts records together.  In this case we are putting the original record from the Ticket Tracker together with a record that has the columns you want added.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Ah ok I understand,

 

I'm having a really hard time getting this to work, Here is what I got, and here is my full code:

 

Yes... I still got to stop using SelectedText

    Filter(
        ForAll('ESN Ticket Tracker' As _item,
            With(LookUp('Networking Device List Master', 'DNS Entity Name' = _item.cj7y),
                Patch(_item,
                    {City: City,
                     State: State
                    }),),),)

Full Code:

SortByColumns(
    Filter(
        AddColumns(
            'ESN Ticket Tracker' As _item,
            "City",
            LookUp(
                'Networking Device List Master',
                'DNS Entity Name' = _item.cj7y,
                City
            ),
            "State",
            LookUp(
                'Networking Device List Master',
                'DNS Entity Name' = _item.cj7y,
                State
            ),
            "Local Contact Name",
            LookUp(
                'Networking Device List Master',
                'DNS Entity Name' = _item.cj7y,
                'Local Contact Name'
            ),
            "Entity Hostname",
            LookUp(
                'Networking Device List Master',
                'DNS Entity Name' = _item.cj7y,
                'Entity Hostname'
            ),
            "DNS Entity Name",
            LookUp(
                'Networking Device List Master',
                'DNS Entity Name' = _item.cj7y,
                'DNS Entity Name'
            ),
            "FWS IP Address",
            LookUp(
                'Networking Device List Master',
                'DNS Entity Name' = _item.cj7y,
                'FWS IP Address'
            ),
            "Verizon IP Address",
            LookUp(
                'Networking Device List Master',
                'DNS Entity Name' = _item.cj7y,
                'Verizon IP Address'
            ),
            "Location Name",
            LookUp(
                'Networking Device List Master',
                'DNS Entity Name' = _item.cj7y,
                'Location Name'
            ),
            "Local Contact Email",
            LookUp(
                'Networking Device List Master',
                'DNS Entity Name' = _item.cj7y,
                'Local Contact Email'
            ),
            "Local Contact Cell Phone",
            LookUp(
                'Networking Device List Master',
                'DNS Entity Name' = _item.cj7y,
                'Local Contact Cell Phone'
            ),
            "Local Contact Office Phone",
            LookUp(
                'Networking Device List Master',
                'DNS Entity Name' = _item.cj7y,
                'Local Contact Office Phone'
            ),
            "Location Street",
            LookUp(
                'Networking Device List Master',
                'DNS Entity Name' = _item.cj7y,
                'Location Street'
            ),
            "Location Zip Code",
            LookUp(
                'Networking Device List Master',
                'DNS Entity Name' = _item.cj7y,
                'Location Zip Code'
            ),
            "Data Access Type",
            LookUp(
                'Networking Device List Master',
                'DNS Entity Name' = _item.cj7y,
                'Data Access Type'
            ),
            "Circuit Speed",
            LookUp(
                'Networking Device List Master',
                'DNS Entity Name' = _item.cj7y,
                'Circuit Speed'
            )
        ),
        Status = 'Status Dropdown'.SelectedText.Value &&
        // This should be changed to not use the deprecated SelectedText property
 ('All States Checkbox'.Value || State = 'State Dropdown'.SelectedText.Value) &&
        // This should be changed to not use the deprecated SelectedText property
 ('All Cities Checkbox'.Value || City = 'City Dropdown'.SelectedText.Value)
    ),
    "Created",
    Descending
)

 

 

I think I'm partly having a hard time because I'm unfamiliar with some of these formulas 🙂 new stuff!

RandyHayes
Super User
Super User

@dlprentice 

Thanks for sharing the entire Formula...that helps.

And yes...you need to stop using the SelectedText property.  It's just a landmine for the future to break.

 

Your formula should be the following:

SortByColumns(
    Filter(
        ForAll('ESN Ticket Tracker' As _item,
            With(LookUp('Networking Device List Master', 'DNS Entity Name' = _item.cj7y,
                Patch(_item, 
                    {City: City,
                     State: State,
                     'Local Contact Name': 'Local Contact Name',
                     'Entity Hostname': 'Entity Hostname',
                     'DNS Entity Name': 'DNS Entity Name',
                     'FWS IP Address': 'FWS IP Address',
                     'Verizon IP Address': 'Verizon IP Address',
                     'Location Name': 'Location Name',
                     'Local Contact Email': 'Local Contact Email',
                     'Local Contact Cell Phone': 'Local Contact Cell Phone',
                     'Local Contact Office Phone': 'Local Contact Office Phone',
                     'Location Street': 'Location Street',
                     'Location Zip Code': 'Location Zip Code',
                     'Data Access Type': 'Data Access Type',
                     'Circuit Speed': 'Circuit Speed'
                    }
                )
            ) 
        ),
        Status = 'Status Dropdown'.SelectedText.Value &&
        // This should be changed to not use the deprecated SelectedText property
 ('All States Checkbox'.Value || State = 'State Dropdown'.SelectedText.Value) &&
        // This should be changed to not use the deprecated SelectedText property
 ('All Cities Checkbox'.Value || City = 'City Dropdown'.SelectedText.Value)
    ),
    "Created",
    Descending
)

 

Alternately, you can also use a ShowColumns method.

This is equivalent to the above:

SortByColumns(
    Filter(
        ForAll('ESN Ticket Tracker' As _item,
            Patch(_item, 
                LookUp(
                    ShowColumns('Networking Device List Master', 
                         "City", "State", "Local Contact Name", 
                         "Entity Hostname", "DNS Entity Name", "FWS IP Address",
                         "Verizon IP Address", "Location Name", "Local Contact Email",
                         "Local Contact Cell Phone", "Local Contact Office Phone",
                         "Location Street", "Location Zip Code", "Data Access Type",
                         "Circuit Speed"
                    ),
                    'DNS Entity Name' = _item.cj7y
            )
        ),
        Status = 'Status Dropdown'.SelectedText.Value &&
        // This should be changed to not use the deprecated SelectedText property
 ('All States Checkbox'.Value || State = 'State Dropdown'.SelectedText.Value) &&
        // This should be changed to not use the deprecated SelectedText property
 ('All Cities Checkbox'.Value || City = 'City Dropdown'.SelectedText.Value)
    ),
    "Created",
    Descending
)

It's shorter, but it is not delegable if that is a concern.

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

@dlprentice 

No problem...just let me know what functions you are in question about.  Happy to elaborate.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Hey @RandyHayes,

 

I've tried using both code blocks but I can't seem to get either to work. I've tried looking over the code, but I'm not seeing where the missing comma should go (if thats what it is).

 

Some questions as well, could you elaborate on "As _item" I've never seen this before, and the best documentation I can find only talks about "As", not "_item".

 

So if I understand how this works is, ForAll is getting all the records, then your using the With function to define each record with the patch function?

 

I've seen some of your other posts, could you give some tips on how you go about applying ForAll, As _item, and the other common iterative functions? Like how do you normally approach these types of data situations to then say "Yes ForAll, and With would work great here with a LookUp". Maybe an example with ['Red','Blue','Green'] would work! 🙂

 

Anyway, hoping to get help with the primary issue on this post, the code. Thanks!

1.png

I realize the LookUp was not being closed. I changed this:

            With(LookUp('Networking Device List Master', 'DNS Entity Name' = _item.cj7y,

 to this:

            With(LookUp('Networking Device List Master', 'DNS Entity Name' = _item.cj7y),

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (2,053)