System client ID and Client Import ID in a Transaction Query

Aaron Spike

We're currently in the testing phase of our migration from FE7 to FE NXT. I've been working to use the Query API to replace some integrations that used direct access to the FE7 MSSQL database. We've hit up against a minor snag. The Query API does not give access to the underlying unique identifiers (i.e. foreign keys) for related records.

In a client query, I can find the "System client ID" and "Import ID" for each client in the "Client Properties" section of the available fields list. Likewise, in a transaction query, I can find the "System transaction ID" and "Import ID" for each transaction in the "Transaction Properties" section of the available fields list. I cannot, however, find a way to retrieve the "System client ID" or "Import ID" for the client related to the transaction in a transaction query.

Does anyone know of a way to access the unique identifiers for related records using the Query API? If not, how are you working around this?

Alex Wong

@Aaron Spike
Are you talking about the Account Receivable > Client object? (in the db schema, AR7CLIENTS?)

If so, you can use “Client ID” to connect the dots.

Aaron Spike

Thanks, @Alex Wong. That’s my current plan, but it isn’t ideal because our business office’s processes require creating client records before the client is has been established in some cases. This is why I was hoping that it was possible to access the underlying unique identifiers.

Alex Wong

@Aaron Spike
I don't quite understand what you mean by “require creating client records before the client is has been established”.

If you create a client record in FE NXT, it will have a Client ID field. if for any reason you want the system record id of the client record in transaction querying, you can “join” the transaction query (programmatically outside of Query API) with the Client query to get the system record id.

Aaron Spike

@Alex Wong That's correct. I believe Client ID is the USERDEFINEDID in AR7CLIENTS table. You'll have to correct me if I'm wrong, but I believe it is possible to configure whether this field is required and automatically generated in FE. In our instance this has been set to not required because there are business processes that require entering transactions prior to the ID being generated in the system that owns those numbers. As I said, it would be better if the System client ID was available because it is unique and always exists, but using the Client ID will be an acceptable work around with the knowledge that it doesn't always work in every situation.

Alex Wong

@Aaron Spike
Wasn't aware that USERDEFINEDID can be blank, i'm not sure if this field can be made “required” per business rule, but business rule is not honor “well” in webview, so that's a drawback.

One way to do this is to automate a checking for USERDEFINEDID being blank, while the fixing (to add/update the USERDEFINEDID field) will have to be manual. Then you can gather data and report using this field.