0

My List contains a "Person or Group" field, and I have figured out that this is a nested table, with fields of its own. But I am having no luck (de)referencing it. My field is named PersonID, and I'd like to run a query from Excel which includes a filter on one of its fields. I think I have learned that the "Person or Group" object may be different by organization, so my object's fields may be different than yours, but so far I can't even get into the object.

I am reasonably certain my RecordSet is configured properly, as I can successfully issue a query such as:

"SELECT * FROM [Table];"

And the PersonID field does come back populated with, I assume, a default field, which, again, I assume is its ID. On the List side, it does not seem to matter which field I configure the List to display... I always just get the ID back.

I specifically want to reference the "Work email" field of the object. When in the PowerApps GUI, I can examine the object, and the column is named "Email." In the List browser settings, however, "Email" is not the name of anything. There is "Work email" and "OtherMail". Again, though, I don't think I'm even getting that far, because for example this throws an error:

"SELECT * FROM [Table].PersonID;"

Error: The Microsoft Access database engine could not find the object 'PersonID'.

I've also tried these, based on something I found online and seemed sensible (if possibly Oracle-related):

"SELECT m.ID, n.Email from [Table] m, table(m.PersonID) n;" and

"SELECT m.ID, n.Email from [Table] as m, table(m.PersonID) as n;" and

"SELECT m.ID, n.Email from [Table] as m, table([Table].PersonID) as n;"

The error for these is: Syntax error in FROM clause, which does seem like I'm getting closer.

Has anyone had any luck dereferencing this object this way?

0

I found the answer to this here.

Not having sufficient reputation, I could not upvote Miloud's answer, which contained the vital detail.

In the connection string, if we specify

RetrieveIDs=No

it appears the default display value comes across, instead of the underlying structure's ID number. I was afraid the setting would remove the record's ID column, which I do find useful, but that was not the case.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.