0

Delete record if fieldA := fieldB

Hi,

is there a way to completely delete the current record if a formula field results in a specific value or equals another given field?

15 replies

null
    • Ninox partner
    • RoSoft_Steven.1
    • 3 yrs ago
    • Reported - view

    delete this

    is the code to delete current record

      • Gianluca
      • 3 yrs ago
      • Reported - view

      RoSoft_Steven Rooryck thank you, but it doesn't seem to work when I use it in a formula field under condition.

      i.e. field C is a formula field and formula is

      if fieldA = fieldB then delete this

      What i get is a notification that "this formula may not change data"

      • Ninox partner
      • RoSoft_Steven.1
      • 3 yrs ago
      • Reported - view

      Gianluca You’re right. It must be triggered. Maybe, if you can,using a trigger after update somewhere? 

    • Mel_Charles
    • 3 yrs ago
    • Reported - view

    Gianluca

    You can put the formula in your trigger after update in the field b 

    as you are asking for the form (record) to delete as soon as there is an exact match on A and B field then this works here .....

    • Gianluca
    • 3 yrs ago
    • Reported - view

    Thanks for your reply RoSoft_Steven Rooryck

    I tried but I can't seem to make it work.

    I'll explain more in details what I have tried, so maybe you can spot my mistake.

    I have a table named People and I have five fields (LastName, FirstName, Sex, Birthplace, Birthdate) and the values in these fields are used to create a unique value (FiscalCode) calculated via formula in another field in the same table (People).

    I thought to put the trigger after update in "Birthplace" (which gets populated last) so that if the newly calculated FiscalCode equals one already created, the record gets canceled.

    This is the code I've used: 

    let xFC := FiscalCode;
if (count(select People where FiscalCode = xFC) = 2) then
delete this
else Birthplace

    I'm using the Mac App and actually I have some problems with Triggers after update in general: they seem so work in a random way (the same formula, in the same field type, sometimes works and sometimes not).🤔 Often I need to close and reopen the app to have the changes applied.

      • Ninox partner
      • RoSoft_Steven.1
      • 3 yrs ago
      • Reported - view

      Gianluca 1.) I would dismiss the formula field you're using now and create a static textfield for that.(In this example called txtFiscalCode)

      2.) Use the trigger after update of your Birthplace field to populate the newly created text-field using the formula of your FiscalCode. (let xFC := PUT HERE THE FORMULA)

      3.) Before assigning the new value into the new field you can already check if there are duplicates found in the table.

      if cnt(select People where Fiscalcode = xFC)>0 then

      let result := dialog("Warning","There are duplicates found, delete this record?",["YES","NO"]);

      if result = "YES" then delete this else txtFiscalCode := xFC end

      else txtFiscalCode := xFC

      end

      ----

      putting this all together in your trigger after update of your Birthplace field would give this: 

      let xFC := PUT HERE THE FORMULA YOU USED IN YOUR FISCALCODE FIELD;
if cnt(select People where Fiscalcode = xFC)>0 then
  let result := dialog("Warning","There are duplicates found, delete this record?",["YES","NO"]);
  if result = "YES" then delete this else txtFiscalCode := xFC end
 else txtFiscalCode := xFC
end

      Hope this helps. Let us know if you need more help.

      Update your old FiscalCode fields into the new created txtFiscalcode fields? Use this in a button: 

      do as server
for i in select People do
i.txtFiscalcode := i.FiscalCode
end
end

      Always make sure to make a backup first !!!!

      Steven

      • Gianluca
      • 3 yrs ago
      • Reported - view

      RoSoft_Steven Rooryck I start to think there are problems/bugs with my App...

      You solution charmed me and I've followed your instructions, but nothing happens at all.

      The formula inserted in Trigger after update in Birthdate field returns no errors, still the newly created textFiscalCode does not get populated by the trigger.

      Just for a check, I also tried to write in trigger after update in the Birthplace field the same exact formula I have in the original FiscalCode formula field: it works perfectly in the formula field, while it produces no results in trigger after update.

      What worries me the most, Is that even very simple formulas seem to behave randomly when used as trigger (i.e I used capitalize(textfield) in two different text fields: in one it returns the expected result, in another the text remains just as it was written. 

      • Ninox partner
      • RoSoft_Steven.1
      • 3 yrs ago
      • Reported - view

      Gianluca It seems to me it's a BUG, I've tried it a moment ago myself and my code doesn't work eighter.(I'm using webbrowser) First my textFiscalcode field was't updated eighter but when creating new records it was updated. But not with the ability to find duplicates.

      Maybe someone else in the community can help us?

      • Gianluca
      • 3 yrs ago
      • Reported - view

      RoSoft_Steven Rooryck 

      • Gianluca
      • 3 yrs ago
      • Reported - view

      i tried again and again, but no luck. I also tried uninstalling and reinstalling the app and the issue is still there. Just to let you know :)

      • Fred
      • 3 yrs ago
      • Reported - view

      Gianluca can you create a sample DB so i can try it out?

      • Gianluca
      • 3 yrs ago
      • Reported - view

      Fred not sure if i exported and attached the right way, but here it is a sample. As you can see, in record number 2 the address doesn't get capitalized (even if the capitalize function is used as a trigger on the field and it worked for both others records). 

      And while the formula for formulaFiscalCode works perfectly, the same formula doesn't work if I use it as trigger after update in BirthDate field (there it actually contains a further instruction, but it's the same if I write it exactly as it is in formulaFiscalCode) to populate textFiscalCode.

       

      Thank you for your concern.

      Sample.ninox
    • John_Halls
    • 3 yrs ago
    • Reported - view

    Hi Gianluca

    The issue you have is that dialogue does not work in Trigger after update: It really needs to be used behind a button. The rest of the code was working for me.

    Regards John

      • Gianluca
      • 3 yrs ago
      • Reported - view

      John Halls hi ..

       I’ve tried also using only the formula without dialog, but I can’t get it to work 😭😭 it’s the same formula that works in formulaFiscalCode, but didn’t work when used as trigger

    • John_Halls
    • 3 yrs ago
    • Reported - view

    Gianluca Take away the dialogue and it works fine for me. That aside, I would not have the fiscal code held in a formula field. I would have the fiscal code held in a text field that is updated with a button, and have it check for duplicates at the same time.

Login to reply

Content aside

  • 3 yrs agoLast active
  • 15Replies
  • 250Views
  • 5 Following