cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dlprentice
Resolver II
Resolver II

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 III
Super User III

@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.
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 III
Super User III

@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.
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 III
Super User III

@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.
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.
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),

 

View solution in original post

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (2,086)