ShowColumns and AddColumns

(0) Share

Report

Posted on by SydEng

Dear All,

I'm having difficulty adding a new column to a collection.

FaultLog is my SharePoint data source.

SubmitDate is in the format ddd dd/mmm/yy (eg MON 28/SEP/20).

I'm wanting to create a new Column "Month" with just the month extracted from SubmitDate.

Reading the documentation I'm reasonably sure I need to use "AddColumns" but can't seem to get it to work in conjunction with ShowColumns

Here's what I have for "OnVisable";

ClearCollect(
FaultReportColl,
ShowColumns(
FaultLog,
"SubmitDate",
"Description",
"Title"
)
)

Thanks for looking,

Ad.

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (1)

Vijay Tailor 2,961 on at

Like (1)

Report
Copy link

Link copied!
HI @SydEng ,

You can Add New Column Like below,>
ClearCollect(FaultReportColl,AddColumns(MenuDataColl,"Month",Text(Today(),"[$-en-US]mmm")));

Hope this solution helps you to resolve your issue.
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
SydEng 68 on at

Like (0)

Report
Copy link

Link copied!

Thanks @VijayTailor,

To be honest I didn't think to make it a separate line, I was trying to make it all in one line.

Using your post (and just Collect on my show columns) I've now got a "Month" column but I can't see reference from the SubmitDate? When I look at the collection all of the months are Sep, not Jul, Aug and Sep as I was hoping.

Here's what I have now:
ClearCollect(FaultReportColl,AddColumns(FaultLog,"Month",Text(Today(),"[$-en-US]mmm")));
Collect(FaultReportColl,ShowColumns(FaultLog,"SubmitDate","Description","Title"))

Cheers,
Ad

Was this reply helpful? Yes No
Vijay Tailor 2,961 on at

Like (0)

Report
Copy link

Link copied!

The below code is enough then.
ClearCollect(FaultReportColl,AddColumns(FaultLog,"Month",Text(Today(),"[$-en-US]mmm")));

if still your issue not resolved plz, share the error details or reference image for the same

Was this reply helpful? Yes No
SydEng 68 on at

Like (0)

Report
Copy link

Link copied!

@VijayTailor
Thanks for persisting with my problem. Reading the code as best I can I don't see where the link is between the SubmitDate column and and Month column. Here's my table that shows the top line having an August submit date.

My thoughts are something along the lines of:
ClearCollect(FaultReportColl,AddColumns(FaultLog,"Month",SubmitDate (something here) Text(Today(),"[$-en-US]mmm")));

Is there a way to remove the day and year info from the SubmitDate column?

Kind regards,

Ad

Was this reply helpful? Yes No
Vijay Tailor 2,961 on at

Like (0)

Report
Copy link

Link copied!

Ohk I Understand your Concern.
but i want your confirmation like what is the type of Column "SubmitDate" like single tine text 0r Date type.

Once you confirm for me , I will work on your issue.

Was this reply helpful? Yes No
SydEng 68 on at

Like (1)

Report
Copy link

Link copied!

Dear @VijayTailor ,

Lucky I'm just about to go home for the day here (Australia).

It's a Sharepoint list as "single line of text".

Cheers,

Ad

Was this reply helpful? Yes No
Vijay Tailor 2,961 on at

Like (0)

Report
Copy link

Link copied!

Ohk Sure, I will post your solution asap. Thanks

Was this reply helpful? Yes No
Verified answer

v-yutliu-msft Microsoft Employee on at

Like (2)

Report
Copy link

Link copied!
Hi @SydEng ,

So your field format is like this MON 28/SEP/20?

If you want to get "9" from MON 28/SEP/20, you could try this formula:

ClearCollect(FaultReportColl, AddColumns(FaultLog,"Month", Month( DateValue( Last( Split(SubmitDat," ") //split MON 28/SEP/20 to two parts: MON and 28/SEP/20 ).Result //get this part 28/SEP/20 ) //transfer text to date type ) //get month from date )

If you want to get "Sep" from MON 28/SEP/20, you could try this formula:

ClearCollect(FaultReportColl, AddColumns(FaultLog,"Month", Left( Last( FirstN( Split(Label4.Text,"/"), //split MON 28/SEP/20 to MON 28,SEP,20 2 ) ).Result, //get SEP 1 //get S )& Lower( Right( Last(FirstN(Split(Label4.Text,"/"),2)).Result, //get SEP 2 //get EP ) ) //get ep )

Best regards,

Was this reply helpful? Yes No