topic Re: One to Many Join or Relates? in ArcMap Questions

One to Many Join or Relates?

FrancisHourigan1 — Wed, 04 Sep 2019 18:39:32 GMT

I Have a polygon feature class with vineyard name 6 letter IDs. I want to join lab data taken for each location but I need to join the data or relate the lab data table in a one to many fashion. The problem is that I want to use a concatenated field in the polygon data that includes the vineyard ID the block and the subblock. I cannot create a new field in the lab data I want to relate it to. Is there a way to create a temporary attribute field that I can concatenate (in model builder or python) to make the related data connection?

Also, I usually use Pro, but the lab table connection is only available in ArcMap. So I need to build the model or script for ArcMap.

Thanks!

Xander Bakker‌ is there an an example python script for something like this?

Re: One to Many Join or Relates?

XanderBakker — Wed, 04 Sep 2019 20:23:38 GMT

Hi francis.hourigan ,

You mention that the data connection is only available in ArcMap. Is this a database connection? To what type of database or data storage? I would think that ArcGIS Pro would be able to connect to all the data formats that ArcMap supports (with the exception of for instance Access MDB files).

It is feasible to create a Python script (or even Arcade) that concatenates two fields to create a key to join the data from the related table. Is there a way that I can have access to the data so I can really see what is going on?

Re: One to Many Join or Relates?

FrancisHourigan1 — Wed, 04 Sep 2019 21:09:40 GMT

Hi Xander,

The database is built on an old DB2 and I'm using an OLE connection in ArcMap to view the tables in ArcCatalog.

I will send you a couple of screenshots via email.

-Francis

Re: One to Many Join or Relates?

XanderBakker — Wed, 04 Sep 2019 21:23:11 GMT

Hi Francis Hourigan ,

Sure, you have my email. I suppose that you can run a script in the python window that and use the table as present in the TOC in ArcMap in order not to worry about the way to access he data. If you have the possibility to migrate that data into something more recent, that would help too, but that is not a required.

Re: One to Many Join or Relates?

XanderBakker — Thu, 05 Sep 2019 12:38:02 GMT

Hi Francis Hourigan ,

So, you have a polygon layer VineyardSubBlocks and a field called "BIMS Vineyard Code" (this is the alias, you will have to use the real fieldname in the code). an example value for this code would be "VNWOO1565151".

In a table called "BIMSPRG.FHARVDTL" you have a couple of fields that should be concatenated: VINEYARD_CODE (VNWOO), VINEYARD_BLOCK (15651) and VINEYARD_SUBBLOCK (51) to get that same code. What you are trying to obtain is the LAB_CODE, which is some case may have multiple records related. In that case you want to obtain the list of values like: "BXR, TA, PH". Is that correct? In which field of the VineyardSubBlocks do you want to store the result?

Re: One to Many Join or Relates?

XanderBakker — Sat, 11 Dec 2021 17:05:47 GMT

Hi Francis Hourigan

If the above assumptions are correct, I think this could work, (but I haven't been able to test the code).

def main():
    import arcpy

    fc = r'VineyardSubBlocks'
    fld_code = 'BIMS_Vineyard_Code' # use correct field name, no alias
    fld_lab_codes = 'LAB_CODES' # output field to store results (must exist)

    tbl = 'BIMSPRG.FHARVDTL'
    fld_yard  = 'VINEYARD_CODE'
    fld_block = 'VINEYARD_BLOCK'
    fld_subblock = 'VINEYARD_SUBBLOCK'
    fld_lab_code = 'LAB_CODE'

    # create a list from table
    flds = (fld_yard, fld_block, fld_subblock, fld_lab_code)
    lst_tbl = [["{}{}{}".format(r[0], r[1], r[2]), r[3]] for r in arcpy.da.SearchCursor(tbl, flds)]

    flds = (fld_code, fld_lab_codes)
    with arcpy.da.UpdateCursor(fc, flds) as curs:
        for row in curs:
            code = row[0]
            lab_codes = GetLabCodes(code, lst_tbl)
            row[1] = lab_codes
            curs.updateRow(row)


def GetLabCodes(code, lst_tbl):
    lst = [d[1] for d in lst_tbl if d[0] == code]
    if len(lst) > 0:
        result = ", ".join(lst)
    else:
        return None # or "" as you prefer

if __name__ == '__main__':
    main()‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: One to Many Join or Relates?

FrancisHourigan1 — Thu, 05 Sep 2019 16:07:32 GMT

Thanks, Xander. I will test it and let you know if it works. However, I was wondering if the LAB_CODES field needs to be, or will be, created in the BIMSPRG.FHARVDTL table? I can't create that field or alter the table in any way. I'm assuming this code creates an intermediate join field, that is not present currently in the table and is not saved once the join is made?

Re: One to Many Join or Relates?

XanderBakker — Thu, 05 Sep 2019 22:01:57 GMT

Hi Francis Hourigan ,

The code as designed at this moment, would write the lab code or lab codes from the related table into the vineyard sub blocks featureclass in a field that is assumed to be present. If that is not what you want, it would be possible to write the result to the screen and not modify the data. However, if you want to join the featureclass with the table you will need to have the join field in your table. In this case it reads the table (no data is modified) and creates the code and "joins" the information to the featureclass. It is not a real join that is happening, in fact it will use the code to find the corresponding lab codes and write them to your featureclass with the sub blocks.