Check if record exists in a table

[jja]

Hi

I have a dataverse table "Transactions" for registering transactions(tasks or jobs made with some items). And The items table "Components". 

Component is some kind a item let's say metal pipe. And it mighrt be that 1 item has 10 metal pipes means we need to produce 10 pcs total. The overall production has multiple stages: Assembling, Painting etc. When user makes some task i register(patch separate operation) this item in table transactions. 

Let's say one day user managed to Assemble 5 out of 10 psc and he registered 5 and record is created in table transactions.

How to perform a check so that user next time can register also maximum 5psc or let's say if maximum psc is reached for that particular stage user is not allowed to create record into Table Transactions. For this i am using canvas app

[jja]

@RandyHayes maybe you can help?

[sopatte]

LookUp will return Blank if there is no record matching. 

So 

With({
  ExistingRecord: LookUp(
    Components,
    SomeField = SomeValue
    && SomeOtherField = SomeOtherValue
  )
},
  If(IsBlank(ExistingRecord),
     ThenStuff,
     ElseStuff
  )
)

[jja]

@sopatte 

Well what if there is multiple records as i described. User can register the task done within multiple records and i must check so he does not register it more that it should be

For example:

Total - 10 psc

with first records user registerd 5 psc

with second record 3 psc

And with last record it should allow him to register not more then 2 psc because total is 10

[sopatte · Accepted solution]

Sum( Table, NumericalFormula )

Set(ExistingPcs,
  Sum(
    Filter(
      Components,
      SomeField = SomeValue,
      SomeOtherField = SomeOtherValue
    ),
    SomeFieldToSum
  )
)

[jja]

@sopatte 

I have 2 errors with the expression you provided

1.

2. Delegation warning

Can you help me to solve this?

[sopatte]

I don't understand what you're trying to do. 

What are you looking up here? Is there a column in your Transactions table that matches the contents of the "Projekto pavadinimas" column of Projects? Likewise, is there a column in your Transactions table that matches the contents of the "Komponento pavadinimas" column of column of Components?

Assuming the answer to both of those questions is yes, and those columns are "Project" and "Component":

Set(ExistingPcs,
  With({
    TargetProject: lblProjektoNumeris.Text,
    TargetComponent: lblKomponentoPavadinimas.Text
  },
  Sum(
    Filter(Transactions,
      Project = TargetProject,
      Component = TargetComponent
    ),
    Kiekis
  )
)

This has to be in a behavior property like OnSelect or OnChange. It can't be in a value property like Text or Default. If you're trying to set a value property, eliminate the outer Set(ExistingPcs,...); the inner part will just return the sum of the Kiekis columns of all matching records.

Note I did not see a reason why you are looking up records in your tables that match the values of your labels' Text only to return the value that matches your labels' Text, so I eliminated those LookUps and just used the labels' Text.

[jja]

@sopatte 

I am very sorry i got this overcomplicated in my mind. i don't have to use lookup function. I got this to work from your first example except i did not use set function. Can you describe why you use set and what it gives? I used this formula and works like a charm

Sum(
Filter(
Transactions,
'Komponento nuoroda'.'Komponento pavadinimas'=lblKomponentoPavadinimas.Text,
'Projekto nuoroda'.'Projekto pavadinimas'=lblinpProjektoNumeris.Text,
'Darbo tipo nuoroda'.'Darbo pavadinimas'=ComboBox1.Selected.'Darbo pavadinimas'
),
Kiekis
)

[sopatte]

Set simply stores the returned value in a variable, in this case the variable name was "ExistingPcs". In your case I guess you just wanted that value to be displayed in a control or something like that. So, a variable was not needed for your purposes.

Set function in Power Apps - Power Platform | Microsoft Learn