Clean a lookup field populated

[Enigma20xx]

I have a join/xref table where I populate two different ID fields throug two differente relationships.

 

Let´s say, I set an invoice number and if that invoice it´s paid, the Invoice Paid ID field gets populated in the join/xref table. If it's not paid, the Invoice UnPaid ID field gets populated.

 

The thing is. If I have to "re set" the invoice as paid/unpaid, when I reselect it again, the other field (paid/unpaid) gets populated, but the first one remains with data.

 

What I want to accomplish is, how could I like "toggle" between the two fields (one gets full, the other gets empty). I'm not very good at relookup scrip step yet.

 

Any advice?

Edited February 24, 2023 by Enigma20xx

[comment]

I find this very confusing.

First of all, why would there be a field in a join table describing whether an invoice has been paid or not? Surely that's an attribute of an invoice and should be recorded (or determined) in the Invoices table - and nowhere else?

And - even more puzzling - why would you need 2 fields to record a single fact? If an invoice has been paid, then there should be a value (e.g. 1 or preferably a date) in the Paid field. If the field is empty (or say contains a 0), then you already know it has NOT been paid. Having 2 mutually exclusive fields is entirely redundant and exposes you to the danger of update anomaly (as you yourself describe).

And what does all this have to do with IDs or lookups?

 

[Enigma20xx]

Dear comment, you as precise as always.

I am not an english native speaker, so I've tryed to make an easy example.

The real situation is: it is a meeting, and there are attendees, and they can vote different given items on the agenda.

There are many attendees but not all of them can vote.

So I collect all the IDs to count how many attendees there are. And I need to separate those who can vote from those who can't.

Attendees are taged as "voters"/"no voters". When I choose their names via 3 different lookups I collect ID (total), ID (no voter), ID (voter). To this two last I have two different relationships ... constant equal/not equal tag voter. It does the work, but if I have to change an attendee, for example from no voter to voter. I go to the table people and tag or untag him. Then I want to reselect him again without deleting him first. I get his data "refreshed", and there is where I need to delete the already filled ID voter/no voter field content and leave only the ID in the correct field updated (so now the count of each group is correct).

I hope the explanation is not even worse than before, lol.

Edited February 24, 2023 by Enigma20xx

[comment]

To answer your question as asked (or more precisely, as I understand it now), you need to uncheck the "Don't copy contents if empty" option in the lookup settings for the field. This way, when you reselect a person with an empty value in the source field, the target field will also become empty.

However, I would still urge you to re-examine your entire setup. If a person can vote, then there should be ONE field in the People table indicating so (either by a Boolean value, or by the effective date). And if a person's privilege to vote can change, and you need to record their status at the time of the meeting, then you want to lookup this field into the Attendance join table. Still only one field. 

I cannot see the need for the additional relationships either. Still, if you want, you could have 2 calculation fields in the Attendance table to return the person's ID as VotingID or NonVotingID. Since these calculations are based on the local (selected) PersonID and the looked up voting status field, they can be stored and used as match fields for relationships. But again, I suspect you're just overcomplicating the thing.

 

[Enigma20xx]

You are right. The 2 calculation fields does de job and makes it easier.

Thank you very much comment.