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?
-
delete this
is the code to delete current record
-
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"
-
Gianluca You’re right. It must be triggered. Maybe, if you can,using a trigger after update somewhere?
-
-
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 .....
-
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 BirthplaceI'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.-
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 endHope 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 endAlways make sure to make a backup first !!!!
Steven
-
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.
-
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?
-
RoSoft_Steven Rooryck
-
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 :)
-
Gianluca can you create a sample DB so i can try it out?
-
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.
-
-
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
-
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
-
-
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.