Unable to use a lookup in collection

[dpjatoch]

Hi all,

I've been stuck on this for a while now and hope you guys have some insight as to why my code isn't working.

Basically i'm collecting records with 2 lookups from a gallery using checkboxes

However, when using forall in the checkbox collection to patch a new record one of the lookups doesnt find the table, even though autofill completes it. Quite sure the code is correct LookUp(Table,Field=something) As it does work outside of the for all collection.

Any idea's? One field matches, the other doesnt.

[RandyHayes]

@dpjatoch 

Can you supply the formulas you are working with and having problems and a little more detail on what is not working properly (i.e. what the results are that you're seeing)?  This would be helpful as you are mentioning collections and ForAll.

[dpjatoch]

Hi Randy,

I was just reading one of your posts about using ForAll wrong ( Using it like a for each in normal development languages).

What I'm trying to do is patch all records in my collection to a new Location, preferably "merging" them to an existing record if it exists based on some checks.

ForAll(
Coll_Checked,
Patch(
Voorraad,

{
bx_Location: LookUp(bx_Location,Name=varLoc),
bx_name: ThisRecord.bx_name,
bx_productnumber: ThisRecord.bx_productnumber,

bx_supplier: LookUp(account,'Account Name'=ThisRecord.'Account Name')
}
)
);

I cut out some things for readability. Basically the account lookup is working fine, the other isnt. I understand that my logic is wrong, according to some of your posts.

Would the correct logic be : 

First filter to find a matching record
Then patch for all?

edit:
Forgot to thank you for your time! Thank you in advance

[RandyHayes]

@dpjatoch 

No problem on the time - happy to help!

Yes, the ForAll in your formula is backward and will lead to poor performance and issues.

Your formula should be: 

With({_location: LookUp(bx_Location, Name=varLoc)},
    Patch(Voorraad,
        ForAll(Coll_Checked As _item,
            With({_account: LookUp(account, 'Account Name'= _item.'Account Name')
            {
             bx_Location: _location,
             bx_name: _item.bx_name,
             bx_productnumber: _item.bx_productnumber,
             bx_supplier: _account
            }
        )
    )
)    

I assume you are working with DataVerse and not SharePoint!?

The above will perform much better just to begin with as the common lookup is done just once rather than every record from your collection.  The other Lookup is then done record by record.  The ForAll produces a table that is given to the Patch function to then (in this case) create a new record.

I assume you are creating records...if not, what is the primary key of your list/table?

Also, if you are working with a collection, then the ForAll can all be skipped if your collection has the same schema as the list/table.  But that is a different story.

[dpjatoch]

Thank you so much, 

I am indeed working with dataverse. 

The collection has the same data as the list/table. Might just do it without forall then. (so my selected collection will all get processed either way?)

I do want to make it a bit more complicated. Seeing as this is about products(productamounts), what i'm trying to do is actually "move" products to a different location, but preferably merge them if that location already exists. Do you think it will work like this? 

I didn't add it earlier but i'm also counting numbers. In single transfers im just calculating old + new. Will this also work in the same way? 

#1 - 1 + 2

#2 - 3 + 6

#3 - 9 + 10

Apologies if my post is a bit chaotic, long day trying to solve this 😁

[RandyHayes]

@dpjatoch 

You can certainly use just the collection (i.e. Patch(yourTable, yourCollection) ) but the key to that is the collection needs to not only have the exact schema and proper value types, but it also needs the primary key.

If you collect it with a direct pull from the table (i.e. Collect(yourCollection, yourTable) ) then the collection will be "pure" and work.  If you manipulate the collection in other ways (such as creating your own column names or transposing data types) then it will not work properly and a ForAll is a great tool to reverse and normalize it.

As for moving to another location...this will all work except...you don't want to deal with the primary key.  You will want to blank it out in order to create new records.

In the case of a merge, then you want to pre-lookup and populate the primary key.

For example, (I will use ID as the primary key for simplicity), the following will merge records based on a Title:

Patch(newList,
    ForAll(oldList As _item,
        Patch(_item, {ID: LookUp(newList, Title=_item.Title, ID)})
    )
)

[dpjatoch]

My primarykey is a guid, but I did an autonumber column because I found that the guid was not easily used or matched.

I guess I don't manipulate the collection (If i check data inside of it, it has the exact data I want). However for lookups you do need to match part of the lookup to text. 

I have written code to change a single record, which correctly matches. isn't this the same, except I will need to use the whole checkbox collection as my basis now?

Tomorrow I'l give it another look. Thank you so much for all your help, it was very educative.

[RandyHayes]

@dpjatoch 

Yes, if you have a collection based on original values, then you can just use that.  

You mention this is a "Checkbox collection"...if you are trying to collect records from a Gallery of items when a box is checked, then you can even do away with all of that extra work.  The Gallery already is a table/collection.  It can be used as the basis for a patch with a filter for only items that are checked.

Yes, give some things a go again tomorrow and respond back with questions.

[dpjatoch]

Hi Randy,

I'm trying to adapt the code you wrote in your previous post, it doesn't work though.

or example, (I will use ID as the primary key for simplicity), the following will merge records based on a Title:

Patch(newList,
    ForAll(oldList As _item,
        Patch(_item, {ID: LookUp(newList, Title=_item.Title, ID)})
    )
)

Patch(Voorraad,
ForAll(Coll_Checked As _item,
Patch(_item,
{ex_Locatie: LookUp(Locatie,Name=varLoc2)}
)
)
)

This results in an error for me, stating invalid arguement (Table), expecting a record instead.

[dpjatoch]

Hi Randy,

I'm trying to adapt the code you wrote in your previous post, it doesn't work though.

or example, (I will use ID as the primary key for simplicity), the following will merge records based on a Title:

Patch(newList,
    ForAll(oldList As _item,
        Patch(_item, {ID: LookUp(newList, Title=_item.Title, ID)})
    )
)

Patch(Voorraad,
ForAll(Coll_Checked As _item,
Patch(_item,
{ex_Locatie: LookUp(Locatie,Name=varLoc2)}
)
)
)

This results in an error for me, stating invalid arguement (Table), expecting a record instead.

I've also tried the other part, but I can't reach any fields except the ones I add in the with.

So I can only reach the location and supplier table