cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
powerwood
Helper II
Helper II

RFI Sequence

I am using Powerapps to create a Request for Information (RFI) process and log. I am using MS Dataverse to store the data. I have a column in Dataverse called "RFI No", which is a text column. I also have a separate table in Dataverse that has the "Job No". I am trying to write a formula in Powerapps that will make the Job No the prefix and then add the next sequential number for that Job No."

 

For example, let's say we have three different Job Nos (21-101, 21-102, 21-103) and then I want to create a new RFI for each one of those. So the RFI No should be 21-101-01, 21-102-01, 21-103-01, respectively. Then when I add more RFIs to those jobs the sequence would continue based on the last RFI No for that job.

 

Once I know how to do that, I can add that portion to the concatenated statement.

1 ACCEPTED SOLUTION

Accepted Solutions
WarrenBelz
Super User III
Super User III

Hi @powerwood ,

Assuming that you will always have 6 characters before the number, the next value would be (dealing with a current record - I will call the current Text Box displaying it RFIText in the example

With(
   {
      wRFI:
      First(
         Sort(
            Filter(
	           YourListName,
		       'RFI No'=RFIText
            ),
	        'RFI No',
            Descending
         )
      )
   },
   Left(RFIText,6) & 
   Text(
      Value(
         Right(
            wRFI,
            Len(WRFI)-6
         )
      )+1
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

8 REPLIES 8
WarrenBelz
Super User III
Super User III

Hi @powerwood ,

Assuming that you will always have 6 characters before the number, the next value would be (dealing with a current record - I will call the current Text Box displaying it RFIText in the example

With(
   {
      wRFI:
      First(
         Sort(
            Filter(
	           YourListName,
		       'RFI No'=RFIText
            ),
	        'RFI No',
            Descending
         )
      )
   },
   Left(RFIText,6) & 
   Text(
      Value(
         Right(
            wRFI,
            Len(WRFI)-6
         )
      )+1
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

WarrenBelz
Super User III
Super User III

Hi @powerwood ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

powerwood
Helper II
Helper II

Hi @WarrenBelz ,

 

Thank you for the help. I have some more work to do to make this work, and I know that I will have some follow up questions. But I haven't worked it out yet to the point where I know what those questions are. 

 

I see what you're doing here, and I appreciate the help.

Hi @WarrenBelz ,

 

I played around with this and I'm having difficulty getting the data to where it needs to be to work in the formula you provided. Perhaps I can provide some more information to help inform a solution.

 

I am storing the data in a Dataverse Table named "RFIS". Here is a gallery view of some of the data.

powerwood_0-1608562711991.png

 

I have a form in which I have a dropdown that sorts a column "Job No" from another Dataverse Table, "Bids". Once I select the Job No., I am trying to have the next RFI No. populate (highlighted in green below). The "Job No." in Bids corresponds with the "Job No." in the RFI table.

 

I'm mentioning this connection in case it makes a difference. I otherwise know how to make the data work across those two tables, so I'm really concentrating here with generating the next sequence for the RFI No. and then also creating the first number "01" for the first RFI for a given Job No.

powerwood_1-1608562877406.png

 

I'm not sure if this additional information provides more insight and alters the above solution you kindly provided earlier.

 

Thank you for your help with this.

 

@WarrenBelz 

 

I was able to generate the last RFI No. for a given Job No. by using Last Filter.

 

When I plugged that field into the formula from the first response, I was not able to get it to work. I'm not sure why.

@WarrenBelz ,

 

I was able to resolve this by taking the part of the formula you created, "Left" and "Text" formulas and incorporating them into this new design. Though, it's not as elegant as the one you created. It does the job.

 

Thank you for the insight and help with this.

Thanks @powerwood ,

I have been offline for a day or so, so good you got it sorted.

russNW
New Member

@powerwood I need to do this exact same process at my company, but I am clueless about where to start.  Any chance I could hire you to do this for us?

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,187)