cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dorinda
Super User
Super User

Concat, Filter and Distinct all i none formula

I have a form and in that form I am creating a unique Title of the record.  I am using this code

 

Concat(Filter(Customers,Title = DataCardValue11.Selected.Result), CompanyCode & "-"  & 'Contract/Agreement Number' & "-" & Year(Now() )) & "-"& Month(Now()) & "-" & 1 & "-" & "AppPermit"

Worked great until I added more lines to the Customers table adding alot more complexity.  I need to just get the Distinct information per customer for the concat piece.  it is returning over and over for all of the customers that have that same Customer Name.

Dorinda_0-1654013074049.png

 

How can I combine this all together?

1 ACCEPTED SOLUTION

Accepted Solutions
Dorinda
Super User
Super User

@rubin_boer Thank you so much for helping me get this fixed.

 

This code worked perfect!

With(
    {//this will return only one record and the string containing your unique record.
        _record: First(
            Filter(
                Customers,
                Title = DataCardValue11.Selected.Result
            )
        )
    },
    _record.CompanyCode & "-"  & _record.'Contract/Agreement Number' & "-" & Year(Now()) & "-"& Month(Now()) & "-" & 1 & "-" & "AppPermit"
)

View solution in original post

12 REPLIES 12
rubin_boer
Super User
Super User

hi @Dorinda 

 

This will return multiple results (when the customer title appears more than once) and thus create the multiple lines

Filter(Customers,Title = DataCardValue11.Selected.Result)

 

Consider a LookUp:

LookUp(Customers,Title = DataCardValue11.Selected.Result).CompanyCode & "-"  & 'Contract/Agreement Number' & "-" & Year(Now()) & "-"& Month(Now()) & "-" & 1 & "-" & "AppPermit"

 

Hope it helps,

R

@rubin_boer 

 

Doesnt like the code it doesnt like the Contract/Agreement Number in the lookup, the real name of the field is Contract_x002f_AgreementNumber and it didnt like that either?

Waegemma
Helper V
Helper V

Hi @Dorinda ,

 

Does this work?

 

 

Concat(Filter(Customers,Title = DataCardValue11.Selected.Result), CompanyCode & "-"  & 'Contract/Agreement Number' & "-" & Year(Now() ) & "-"& Month(Now()) & "-" & 1 & "-" & "AppPermit")

 

 

 

I moved the parenthesis after Year(Now()) to the end.

 

Also possible:

 

Concat(Filter(Customers,Title = DataCardValue11.Selected.Result), Concatenate(CompanyCode,"-",'Contract/Agreement Number',"-",Year(Now()),"-",Month(Now()),"-",1,"-","AppPermit"))

 

 

Marc

 

Dorinda
Super User
Super User

@Waegemma no this is just back to what I had originally.

 

I am getting this now.

 

Dorinda_0-1654023582775.png

It is not giving me the distinct value

rubin_boer
Super User
Super User

@Dorinda interesting, and if you return the First item?

First(Filter(Customers,Title = DataCardValue11.Selected.Result).CompanyCode)

 

@rubin_boer  now it says it is expecting a text value.....no luck

rubin_boer
Super User
Super User

@Dorinda i just ran a test on my side and it seem to work unless i get your datasource wrong.

 

consider the data source multiple entries and the uid as

//data
ClearCollect(
    colItems,
    ForAll(
        Sequence(10),
        {
            id: Value,
            data: "Item : " & Mod(
                Value,
                3
            )
        }
    )
);

//unique id
First(
    Filter(
        colItems,
        data = "Item : 1"
    )
).data & "-" & Today()

 

rubin_boer_0-1654024091201.png

 

am i missing something?

 

Regards,

R

@rubin_boer 

 

Now getting this 

Dorinda_1-1654024590081.png

 

Waegemma
Helper V
Helper V

@Dorindaonly explanation I see, is that your Filter-result is a table with all the same records, but that would be strange

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (4,194)