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

hey there if you liked the post give it a thumbs up, and if it solved your question please accept it as a solution.

@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
Super User
Super User

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)

 

hey there if you liked the post give it a thumbs up, and if it solved your question please accept it as a solution.

@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

hey there if you liked the post give it a thumbs up, and if it solved your question please accept it as a solution.

@rubin_boer 

 

Now getting this 

Dorinda_1-1654024590081.png

 

Waegemma
Super User
Super User

@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
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors
Users online (2,779)