cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
natmaxex
Resolver I
Resolver I

Multi layered sub ID's

I created an Excel spreadsheet with a multi-layer id system like this
IdLayers.PNG

I have ID and DataID setup to generate there ID's the way i want I'm just having a little trouble with the subDataID.
when creating a new form i want to check if DataID value has ever been created before and if so to create a new subDataID value 1 for that DataId value and Increimate subDataID =+1 for every time a new DataID with the same DataID is created.

Maby something like this.

 

 

if(DataIDCard.Text <> LookUp(Table,DataID),Set(subDataIdVariable,1),Set(subDataIdVariable,Max(Filter(Table2,DataID = "10"),subDataID)+1))

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-yutliu-msft
Community Support
Community Support

Hi @natmaxex ,

Do you want to check whether there's subDataID, if yes last subDataID+1, if no start from 1?

If so, the key point is to use LookUp function to get whether there's existing DataID.

You should use formula like this to get field value:

LookUp(table,condition,fieldname)

The formula that you used: LookUp(Table,DataID) will only get a record. You use use IsEmpty to justify whether that's record, not to compare a value with a record.

 

Try this formula:

If(
    IsEmpty(LookUp(Table2,DataID=DataIDCard.Text)),
    Set(subDataIdVariable,1),
    Set(subDataIdVariable,Max(Filter(Table2,DataID = DataIDCard.Text),subDataID)+1)
  )



 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Thanks, I find this works a lot better.

If(
    IsBlank(LookUp(PeoplePurchasDev,DataID=DataCardValue6.Text,DataID)),
    1,
    Max(Filter(PeoplePurchasDev,DataID=DataCardValue6.Text),subDataID)+1
)

View solution in original post

2 REPLIES 2
v-yutliu-msft
Community Support
Community Support

Hi @natmaxex ,

Do you want to check whether there's subDataID, if yes last subDataID+1, if no start from 1?

If so, the key point is to use LookUp function to get whether there's existing DataID.

You should use formula like this to get field value:

LookUp(table,condition,fieldname)

The formula that you used: LookUp(Table,DataID) will only get a record. You use use IsEmpty to justify whether that's record, not to compare a value with a record.

 

Try this formula:

If(
    IsEmpty(LookUp(Table2,DataID=DataIDCard.Text)),
    Set(subDataIdVariable,1),
    Set(subDataIdVariable,Max(Filter(Table2,DataID = DataIDCard.Text),subDataID)+1)
  )



 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Thanks, I find this works a lot better.

If(
    IsBlank(LookUp(PeoplePurchasDev,DataID=DataCardValue6.Text,DataID)),
    1,
    Max(Filter(PeoplePurchasDev,DataID=DataCardValue6.Text),subDataID)+1
)

View solution in original post

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

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.

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