cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jiji
Regular Visitor

Autonumber for line number

Hello,

 

I'm newbie in Dataverse, one question come to mind is how are we manage the column table for Line number. My question is basically related to some business needs that usually have Header and Detail situation like the Order form which usually has Header and Detail Part. 

While Header part is fine, if I need number I can use Autonumber for its Order ID, in the Detail table I would like to have a column for Line Number. Obviously I cannot use Autonumber, because it will make the Line number not reset everytime we change Order ID.

 

How are we usually handle this ?

 

Thanks,

4 REPLIES 4
v-qiaqi-msft
Community Support
Community Support

Hi @Jiji,

Do you want to add a line number for each row?

Could you please share a bit more about the scenario?

Add a Gallery and set the Items property as below:

 

Clear(colLineNumber);
ForAll(YourTable, 
       Collect(colLineNumber,
               Last(FirstN(AddColumns(YourTable,
                                "RowNumber",
                                CountRows(colLineNumber)+1
                           ), 
                           CountRows(colLineNumber)+1
                    )
               )    
       )
)

Set the Text property of the Label within the Gallery as below:

ThisItem.RowNumber

If you want to save this RowNumber to your Dataverse table, please add a save button and set the OnSelect as below:

ForAll(
  colLineNumber,
  Patch(
          'YourTable',
           Defaults('YourTable'),
           {
             TableColumn1: CollectionColumn1,
             TableColumn2: CollectionColumn2,
             TableColumn3: CollectionColumn3,
             ... 
           }
  )
)

 

Best Regards,
Qi
dpoggemann
Super User
Super User

Hi @Jiji ,

 

I have done this in the past following this approach with some caveats... 🙂

 

1.  Create a Whole Number field on the Header called something like "Next Line Number"

2.  Initialize this field to 1 on create of the Header record

3.  When creating the Line then utilize a real-time workflow that will look at the Header Next Line Number and set the line number on the line to this value and next step in the workflow would be to update the Header "Next Line Number" to increment by 1.

 

This has worked well in situations where I have needed to do this type of numbering scheme with some caveats including:

1.  If you delete a line (i.e. line 2) you will have lines 1, 3, 4, 5, etc. and #2 will be gone 

2.  I have not tested this with high volume situations where I am creating multiple lines at a time where you could have some concurrency issues.

 

Hope this helps.  Please accept if answers your question or Like if helps in some way.


Thanks,


Drew

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew

Hi @dpoggemann 

 

Yeah.. looks interesting. Looks like I need to learn about real time workflow first. Will get back to here and inform the result.

 

Thanks.

An alternative to real-time workflows would be to do this in a plugin, which gives you a bit more insulation. You still would need to be concerned with concurrency, but in a plugin you can use some tricks to get around this.

 

For one, the plugin can run on create/update/delete, allowing you to grab all the line items and number them all at once. This addresses the numbering gap issue @dpoggemann mentioned, but it does so at the cost of needing to write a fair bit of code, since you'll need plugins on all three of those steps.

 

Then, as far as concurrency, this is a trickier problem but it can be circumvented a couple ways. The easiest--if you don't mind the numbering being an async operation--is to trick your running plugins into executing at different times, even if they all kick off at the same time. You can do this by adding a thread.sleep() for a Random() value of milliseconds. The max of random directly corresponds to the effectiveness of this approach, so if you think you'll almost never get more than 3 operations happening simultaneously, maybe you have the random go from 0 to 10000ms, or if you might have 30 being added at once, maybe you would go from 0 to 50000 or 100000 or whatever number you want. 

 

Now, these are adequate workarounds, but neither is perfect. For sure and for certain, setting up a real-time wf for this is your fastest and easiest solution. The Plugin options I've outlined add some additional control and insulation against scalability problems, but they do require a fair bit of coding.

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Users online (5,076)