I am trying to join records from Table B (join table) to Table A (input table) in ArcGIS Pro using the Join Field tool. This is a one-to-many join such that there are multiple records in Table B that match with a single record in Table A. The results of the join are not what I expect given the parameters I specified.
For the numeric fields from Table B, I am using the "Field Mapping" transfer method and specifying "Mean" as the merge rule. My goal/expected result is that the average value of all the matching records in Table B is joined to the corresponding record in Table A. For the text field from Table B, I am using the "Field Mapping" transfer method and specifying "Join" as the merge rule with ";" as the delimiter - my goal/expected result is that the text from all the matching records in Table B will be concatenated, with each string separated by ";", and joined to the corresponding record in Table A.
The result that I am getting is that only the first matching record from Table B is getting joined to the corresponding record in Table A. This is happening for both the numeric fields and the text field. It appears as though the Join Field tool is not adhering to the Field Mapping and merge rules that I have specified.
I saw in the documentation for Join Field that "To perform a one-to-many join, the Input Table parameter value must have an Object ID field and be in the same workspace as the Join Table parameter value." I believe I have these requirements satisfied as both my tables are located within the same file geodatabase.
Below are some screenshots to help illustrate the results and identify the problem. First, I provide a screenshot of the parameters I used in the Join Field tool. Each panel shows the specific field mapping parameters for each of the three attributes involved in the join, highlighted with blue boxes:
Next, a screenshot showing my spatial data with the relevant attributes highlighted. The two points are the "join features" - they contain the attributes that I need to join to the polyline. I put a blue box around the three attributes that I am using in the join. The polyline is the "input feature" - the feature to which the attributes from the points should be joined. The join is made based on the "NEAR_FID" attribute in the join features, and the "OBJECTID" in the input features (circled in green). I put a purple box around the actual results of the join, which I believe are incorrect based on the parameters I used for the join.
Based on the parameters I used in the Join Field tool, I would expect the following results in the attribute table of the "input feature" (the polyline dataset):
