LookUp function returning null although the value is in the database

[Splush]

Hey,

so Ive ran into a weird problem recently and cant seem to fix it.

I wonder if you guys might have a solution.

So this is my patch function that I call when somebody scans a product in the app.

Patch(
            'dbo.ClearedProducts';
            Defaults('dbo.ClearedProducts');
            {
                AKID: Clearing_AKID_Value.Text;
                EAN: Clearing_EAN_Value.Text;
                EE: MA;
                CUSTOMER: KdNr;
                LP: If(
                    IsBlankOrError(
                        LookUp(
                            'Inventory';
                            AKID = Clearing_AKID_Value.Text;
                            'Storage space'
                        )
                    );
                    "XXXX";
                    LookUp(
                        'Inventory';
                        AKID = Clearing_AKID_Value.Text;
                        'Storage space'
                    )
                );
                articleNumber: If(
                    IsBlankOrError(
                        LookUp(
                            'Inventory';
                            AKID = Clearing_AKID_Value.Text;
                            'Article number'
                        )
                    );
                    "tbd";
                    LookUp(
                        'Inventory';
                        AKID = Clearing_AKID_Value.Text;
                        'Article number'
                    )
                );
                EXEC: Now()
            }
        );

It works perfectly 99% of times.

Though every now and then the App inputs "tbd" into the "Clearing" table although all products in the "Inventory" table do have an article number.

Its even weirder since this does not happen with the "storage space" field (They are both strings by the way).

If this matters in any way, we use an azure cloud database for this project.

Do you guys have any idea on how to solve this?

[WarrenBelz]

Hi @Splush ,

Possibly not the issue, but try this structure

With(
   {
      wInvent:
      LookUp(
         'Inventory';
         AKID = Clearing_AKID_Value.Text
      )
   };
   Patch(
      'dbo.ClearedProducts';
      Defaults('dbo.ClearedProducts');
      {
         AKID: Clearing_AKID_Value.Text;
         EAN: Clearing_EAN_Value.Text;
         EE: MA;
         CUSTOMER: KdNr;
         LP: 
         Coalesce(
            wInvent.'Storage space';
            "XXXX"
         );
         articleNumber: 
         Coalesce(
            wInvent.'Article number';
            "tbd";
         );
         EXEC: Now()
      )
   }
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[Splush]

Dude,

Im so thankful for you showing me the coalesce function already - Ive never used it before but it saves so much time and code.

Also I will try this structure asap.

[WarrenBelz]

@Splush ,

No problems - you might also look at the time/typing the With() function saves.

[Splush]

Although these functions so save a lot of time and Im really glad that I now use them, this sadly was no fix for my original issue. Today I searched the database and found 5 more articles with "tbd" as article number.

The rest of the values like ean, pznr and upc dont get pached aswell.

I relly dont get it. The database only has 5k values stored so its not even that big

[WarrenBelz]

Hi @Splush .

How many items are in Inventory that match the top filter and what is your delegation limit set at?

[Splush]

The AKID is a serial number so its just one item matching the filter and LookUp is delegable. The limit is set to 2.000.

What I now did is just create a flow that will patch the missing items each hour but that really is not a good solution 😕

[WarrenBelz]

Hi @Splush ,

I was just excluding things - yes the top filter will return a record variable, so the values in that record for the fields 'Storage Space' and 'Article Number' will be available in wInvent as your filter on the Text field AKID matching the Text box is Delegable (LookUp still needs a Delegable field type and operator). You can try "the long way" instead of the Coalesce statement, but they are the same thing. for a bit of debugging, you can do this

UpdateContext(
   {
      varInvent:
      LookUp(
         'Inventory';
         AKID = Clearing_AKID_Value.Text
      )
   }
);
Patch(
   'dbo.ClearedProducts';
   Defaults('dbo.ClearedProducts');
   {
      AKID: Clearing_AKID_Value.Text;
      EAN: Clearing_EAN_Value.Text;
      EE: MA;
      CUSTOMER: KdNr;
      LP: 
      Coalesce(
         varInvent.'Storage space';
         "XXXX"
      );
      articleNumber: 
      Coalesce(
         varInvent.'Article number';
         "tbd";
      );
      EXEC: Now()
   )
)

then display in a Label

"Storage Space - " & varInvent.'Storage Space' & Char(10) & "Article Number - " & varInvent.'Article number'

and monitor if the right values appear.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[Splush]

Hi @WarrenBelz,

there is six people running through our warehouse and using this app for thousands of products each day. And only for less than 1-2% of these this error occurs. I dont think debugging is really an option here.
I guess I will just have to live with the power automate solution. Thanks a bunch for your help though!