Can this flow works?

[Axal]

Hi guys,

I created this flow which need your advice whether it is going to work or not ? the main idea is to read my Excel file in One Drive, look into the rows inside then insert into CDS.

So first I created a task -->

then on the next step, I'm using Excel connector and use this action :

In the next step, I have CDS connector, choose my environment and entity and put the fields from Excel to the right column in CDS.

No error from Flow checker. However when I test run, put Excel file in the specified OneDrive, the flow just shows running with nothing happened.

Please advice if that setup need some correction or my logic is incorrect.

Thanks,

Axal

[Axal]

Just an update,

It does failed on the 3rd step : Create new record.

The input section looks good, but the output section, it has Bad request with status code : 400

Does it meant the destination setup has problem ? on which part it is ?

Thanks,

[yashag2255]

Hey @Axal 

There might be some referencing issues here. Can you share a screenshot of the flow error? 

Note that you should ensure that the fields provided from excel to the CDS action the data type should be the same. Can you check and confirm this? Also, can you confirm that there is some output in the excel action? 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[Axal]

Hi,

The data type, I would say it is the same, it is really rely on text input although some of them has a lookup in the end point, but still it is a text. I'm a bit confuse about "Can you confirm that there is some output in the excel action", as I consider Excel is the input only.

About the error, it did actually tell something missing but I didn't find any missing. Is like this (although I have to decrypt some of it)

The error message suggesting missing ")" or "," in position 6, and show the text value that it is one of the text from excel, but I didn't see any missing or the need of ")" or ","

Or is it? but what and where I should put that ?

Thanks in advance,

Axal

[yashag2255]

Hey @Axal 

Can you check and confirm what is the data type of the Billing Customer field in the entity? I am guessing it is a lookup and therefore that field requires you to provide the GUID instead of the name over there. So how this would work is you add a get record or a list records action and first get the guid for that field and then create the record over there. 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[yashag2255]

@Axal 

+edit, basically looks like all these fields that you are trying to update are lookups so you will actually need to get the guids of the respective labels that you have in the excel. 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[Axal]

Well, to have GUID I think is difficult, also un-practical. So does it mean it is not possible to use Flow, if the record is from a Lookup inside CDS ?

The reason I'm trying to set up Flow is actually to ease the work of the user (operator) who insert the record daily.

Thanks,

[yashag2255]

Hey @Axal 

So it is like, you will have to get the guids for lookups apart from that there is no way to directly add that label value from excel directly in the create record action. 

See it like this: You have a customer name in the excel, you can add the get records action and select the appropriate entity and then in the filter query use the fieldname eq 'selecttheexcelcolumnfromdynamicselectorhere' and then this should basically return you only one item as there is just one customer by that name. Now you can create string variables and get the guid from this action here (it will create an apply to each loop but there is just one value here. makes sense?) you will have to repeat that for each excel column that you are trying to add to the create record action where there are lookups. 

Other than that there is no way to create record that involves a lookup. 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[Axal]

Hi, do you have any sample, how and where to do that, or some blog teaching this ?

Thanks in advance,

[Axal]

Hi,

I try to figures this out a bit.

Is it gonna be like this :

So before CreateNewRecord action, I put GetRecord action, with those setup:

- set the environment

- set the entity

- then put the excel column which will be the search criteria.

However it is failed :

Is the criteria wrong ? because the message is said something about item ID whilst the input / search criteria is customer account.

Also am I correct to use Data subject ID in CreateRecord action ? is that the GUID ?

Thanks in advance,

Axal

[yashag2255 · Accepted solution]

Hey @Axal 

The get record action again requires a guid for the identifier. Here is how you can do it: sample: 

Here, I am listing records from accounts entity using the filter query where a particular field is equal to the column 1 item in excel and extracting the account ID. Note that this works only if the filter query brings out a single record from cds. 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[Axal]

Hi,

Thanks for your patience.

As your guidance, I edit my flow to be like this :

My first question is in the Filter query -> are we supposed to write the fieldname manually ? or it supposed to have the field list of the Account table that we can pick ? In my case I manually write it, 'coz there isn't field list, and another thing is whether we should type the field name (or they called it column name) or we can type Display name (which is more readable and probably has space like for example 'account name'.

Second, after that step, I add action "SetVariable" (Noticed at your example is "AppendVariable", but I'm afraid if using "Append", it will bring the old value keep adding after several iteration, or am I wrong ?

However, it is failed when I test run, at step "ListRecord"

Is it related to my 1st question about incorrectly type the filter query ?

Thanks in advance,

Axal

[Axal]

Hi,

Found the culprit. It turns out I should put quote in between the columnname. And it WORKS!

I created variables for all the input that needs lookup and put the variables in CreateNewRecord step. It run through. 

@yashag2255 THANK YOU VERY MUCH FOR YOUR GUIDANCE.

This is the full flow (in summary) :

Thanks again,

Axal