Hey guys, Hopefully I can explain this proper...

expressions
(Park Real Estate App) #1

Hey guys, Hopefully I can explain this properly. So I want to copy over data from one table to another of an address field. Lets say table A is the source table and Table B is the Destination table. In Table B the Address field is the key so that I can reference back to the records in Table A.

Table A is the records table for each contact that is made and where the process begins when a property has not been contacted before for being listed. Table B is in the office where a profile is created and assigned to a property agent. I guess you could say the static table for future use.

So in a form for Table B I am trying to create a drop down list of entries that exist in Table A that do not yet exist in Table B to simplify the process. Does anyone have any better ideas to my current method? Surely there is some standard best practice to this and I am quite new to expressions.

My current formulae is =(SELECT(TableA[Address], Not(IN([Address], TableB[Address]))))

The problem is that when you go to edit the second time the entry is invalid as the address now exists in Table B not allowing you to save.

Thanks for your help.

(Aleksi Alkio) #2

How about TableA[Address] - TableB[Address]

(Park Real Estate App) #3

Thanks Aleksi for the more simple proposal though unfortunately I get the same result. Is there another way I can transfer making sure each entry is unique?

(Park Real Estate App) #4

sigh I thought this was a common issue but obviously not. Thanks for your help guys I will have to find some other way.

(Aleksi Alkio) #5

How about… TableA[Address] - TableB[Address] + SELECT(TableB[Address],[KeyColumn]=[_THISROW].[KeyColumn])

(Park Real Estate App) #6

Holy Sizzle sticks! It works. Thank you.

(Park Real Estate App) #7

Again Thank you.