Patch to D365FO Limit Exceeded

[PytByt]

Hi All..

I using patch and for all formula to send all my collection data to DE in D365FO and find this error about limit exceeded: 



Is there any way to avoid this limit exceeded error and make my patch formula works?

Thanks.

Regards,

pytbyt

[v-siky-msft]

Hi @PytByt 

This issue means you make too many calls to D365 FO and hit the limit of API calls.

The limit of D365 FO API call is 200 calls per minute. Reference: https://sharepains.com/2018/07/17/microsoft-flow-connector-limits/

My suggestion will be to reduce the number of calls being made in the app.

1. Refresh the D365 FO connection

2. Patch data in batches, patch fewer records at one time, e.g. first to patch 100 records, then patch another 100 records at next time.

Best regards,

Sik

[PytByt]

Hi @v-siky-msft 

Thanks for the reply.

I already read the blog and Patch data in batches is what i have in mind also. 

Can you help how am i suppose doing the patch partially?

I'm using Patch formula something like below:

ClearCollect(c_UpdateQtyERP,
Patch(InventoryCountingJournalLines,
AddColumns(
ShowColumns(
Filter(c_InvCountingJnlLine, LineNumber<>0), 
"JournalNumber", "CountedQuantity", "LineNumber", "MIIINV_Description", "InventoryOwnerId", "ItemNumber", "AdjustmentQuantity"
)
, 
"dataAreaId", "smu", "IsCountedQuantityZero", "Yes"
)
)
);

Notify(CountRows(c_UpdateQtyERP) & " Updated Lines sent to ERP Counting Journal " & s_SelectedJournal, NotificationType.Success)

Regards,
pytbyt

[v-siky-msft]

Hi @PytByt 

My idea is to divide the Collection into two parts and patch each part separately.

1. Save the sharped data into Collection

ClearCollect(MyCol, AddColumns(
ShowColumns(
Filter(c_InvCountingJnlLine, LineNumber<>0), 
"JournalNumber", "CountedQuantity", "LineNumber", "MIIINV_Description", "InventoryOwnerId", "ItemNumber", "AdjustmentQuantity"), 
"dataAreaId", "smu", "IsCountedQuantityZero", "Yes"
))

2. Use countrows function to get the row numbers, and save the first half as col1, save the second half as col2

ClearCollect(col1, FirstN(MyCol,Round(CountRows(MyCol)/2,0)));
ClearCollect(col2, FirstN(MyCol,CountRows(MyCol)-Round(CountRows(MyCol)/2,0)))

3. Patch each Collection separately

ClearCollect(c_UpdateQtyERP, Patch(InventoryCountingJournalLines, col1)); 
Collect(c_UpdateQtyERP, Patch(InventoryCountingJournalLines, col2)); 
Notify(CountRows(c_UpdateQtyERP) & " Updated Lines sent to ERP Counting Journal " & s_SelectedJournal, NotificationType.Success)

Please take a try to check if the limit exceeded error is fixed.

Sik

[PytByt]

Hi @v-siky-msft 

Thanks for the reply.

I need to know if the formula will work if my collection if having more than 700 records (1000 record at max)?

And how can i modify your formula for the loop? (like using ForAll function when divide the collection)

Please Help.

Thanks,

pytbyt

[v-siky-msft]

Hi @PytByt ,

If you want to use ForAll function for the loop patch, please try this:

ForAll(col1, Patch(InventoryCountingJournalLines, Defaults(InventoryCountingJournalLines),{FieldA: FieldA, FieldB: FieldB}));

ForAll(col2, Patch(InventoryCountingJournalLines, Defaults(InventoryCountingJournalLines),{FieldA: FieldA, FieldB: FieldB}))

Please take a try to see if this works for your case.

Sik

[PytByt]

Hi @v-siky-msft 

Thanks for the insight, 

Assuming i have 1000 records in my collection that i need to partially patch, how can i divide it by 100 records each time i patch to avoid the rate limit issue based on your formula above?

Please help.

Thanks,

[v-siky-msft]

Hi @PytByt ,

Please try this:

1. Save the sharped data into Collection

ClearCollect(MyCol, AddColumns(
ShowColumns(
Filter(c_InvCountingJnlLine, LineNumber<>0), 
"JournalNumber", "CountedQuantity", "LineNumber", "MIIINV_Description", "InventoryOwnerId", "ItemNumber", "AdjustmentQuantity"), 
"dataAreaId", "smu", "IsCountedQuantityZero", "Yes"
))

2. try this code, then you will get 10 collections and each one stores 100 records.

ClearCollect(Col1,FirstN('SP list',100));ClearCollect(Col2,FirstN('SP list',200));ClearCollect(Col3,FirstN('SP list',300));ClearCollect(Col4,FirstN('SP list',400));ClearCollect(Col5,FirstN('SP list',500));ClearCollect(Col6,FirstN('SP list',600));ClearCollect(Col7,FirstN('SP list',700));ClearCollect(Col8,FirstN('SP list',800));ClearCollect(Col9,FirstN('SP list',900));ClearCollect(Col10,Remove(MyCol,Col9));
Remove(Col9,Col8);Remove(Col8,Col7);Remove(Col7,Col6);Remove(Col6,Col5);Remove(Col5,Col4);Remove(Col4,Col3);Remove(Col3,Col2);Remove(Col2,Col1)

Hope this helps.

Sik

Please click Accept as solution if my post helped you solve your issue.

[PytByt]

Hi @v-siky-msft 

What shoud i do if i don't know or if i can't get the exact total records in my collection?

How can i divide it by looping?

Thanks.

pytbyt

[v-siky-msft]

Hi @PytByt ,

I don't think it is possible since the number of sub-Collections is uncertain and the Collection name must be hardcoded.

As an alternative workaround, We need to hard-code all possible scenarios ahead of time.

Take an example about two scenarios that the records number is between 900-1000 or 1000-1100:

If(CountRows(MyCol)>900 && CountRows(MyCol)<1000,   

ClearCollect(Col1,FirstN('SP list',100));ClearCollect(Col2,FirstN('SP list',200));ClearCollect(Col3,FirstN('SP list',300));ClearCollect(Col4,FirstN('SP list',400));ClearCollect(Col5,FirstN('SP list',500));ClearCollect(Col6,FirstN('SP list',600));ClearCollect(Col7,FirstN('SP list',700));ClearCollect(Col8,FirstN('SP list',800));ClearCollect(Col9,FirstN('SP list',900));ClearCollect(Col10,Remove(MyCol,Col9));
Remove(Col9,Col8);Remove(Col8,Col7);Remove(Col7,Col6);Remove(Col6,Col5);Remove(Col5,Col4);Remove(Col4,Col3);Remove(Col3,Col2);Remove(Col2,Col1),

CountRows(MyCol)>1000 && CountRows(MyCol)<1100,

ClearCollect(Col1,FirstN('SP list',100));ClearCollect(Col2,FirstN('SP list',200));ClearCollect(Col3,FirstN('SP list',300));ClearCollect(Col4,FirstN('SP list',400));ClearCollect(Col5,FirstN('SP list',500));ClearCollect(Col6,FirstN('SP list',600));ClearCollect(Col7,FirstN('SP list',700));ClearCollect(Col8,FirstN('SP list',800));ClearCollect(Col9,FirstN('SP list',900));ClearCollect(Col10,FirstN('SP list',1000));ClearCollect(Col11,Remove(MyCol,Col10));
Remove(Col10,Col9);Remove(Col9,Col8);Remove(Col8,Col7);Remove(Col7,Col6);Remove(Col6,Col5);Remove(Col5,Col4);Remove(Col4,Col3);Remove(Col3,Col2);Remove(Col2,Col1)

)

Hope this helps.

Sik