Possibly Creating a Cross-Reference Script/Function

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • JRender
    Junior Member
    • Mar 2025
    • 15

    #1

    Possibly Creating a Cross-Reference Script/Function

    Hello,

    I help administer a self-hosted EspoCRM server for a colleague, and they recently asked if there was a way to speed up/automate a task. Before I even begin the process of trying to possibly write a formula script/custom function that would accomplish this, I wanted to ask if this is even something that can be done using EspoCRM.

    Here is the task in general: My colleague receives a list from a third party that contains unique identifier numbers, and my colleague needs to check and see if the numbers from the 3rd party list match specific field values of records contained within a single entity of EspoCRM. As part of this process, if any of the numbers from the 3rd party list do not have a matching record, my colleague needs to know what those numbers are (so they can inform the 3rd party). Similarly, if any of the records in the EspoCRM entity have numbers that did not match one of the numbers from the 3rd party list, then they need to know which records those are (so they can inform the 3rd party).

    This is the process that I envision for how it would work:
    Step 1: Input the list of given numbers from the 3rd party into EspoCRM either by copying and pasting them into some kind of input box or uploading a CSV file containing the list.
    Step 2: One by one, take each number from the list and search through records in a specific entity for a matching field value.
    Step 3: Output the numbers from the 3rd party list that don't have a matching record in EspoCRM.
    Step 4: Output the names of the records in the entity that had no matching number from the 3rd party list.

    These are my questions:
    1. Is it even possible to write a formula script/create a custom function that would be able to do all of this?

    2. If it is possible, how complicated would the script/function have to be (bearing in mind that I only know a little bit about programming)?

    ​​​​​Thanks for whatever help you can provide.
  • shalmaxb
    Senior Member
    • Mar 2015
    • 1748

    #2
    Hi, I guess, such process will not be possible with mere formula script, but with a standalone script or perhaps an extension. I recommend to propose this to the developers here in this forum. There are a few, who are very skilled even beyond espoCRM (but still have profound knowledge of this wonderful system). I already have some extensions and scripts being written from other developers. I myself do not have sufficient programming skills, so these developers always are my first approach, when I have an idea, which I cannpot manage with formula.

    Comment

    • JRender
      Junior Member
      • Mar 2025
      • 15

      #3
      Thanks for your input shalmaxb. I may have to look into asking some of the developers for assistance.

      Comment

      • jeffreysgrossman
        Senior Member
        • Jan 2025
        • 120

        #4
        JRender while I can give no assistance on any scripts to perform this. However after reading it I am wondering if you could not just use the import feature set to update only. Then choose the field that contains the number that you want to check to see if it exists in the records. If you use that field checkbox as the update by it will go thru the list you have and update only the records that are in ESPO. It will also then provide you with a list of items that it could not update. Each import is stored in the imports section so you can go back and refence them at a later date. Its not as elegant as a script but it very well might work out of the box with no scripting if I am understanding correctly. See below for screen shots of what I am thinking:

        Here is the basic setup on import:
        Click image for larger version

Name:	image.png
Views:	0
Size:	28.5 KB
ID:	121626In the next section you need to align the field that you want to have checked in the .csv file with that field in that entity. I just randomly chose Delivery Order. Howver the checkbox for update by is the important part:
        Click image for larger version

Name:	image.png
Views:	0
Size:	65.6 KB
ID:	121627Now once done you get a list of all the errored records that did not already exist in ESPO. Yours would also have the list of successes that did match. Mine was random so nothing matched. However I think start to see.
        Click image for larger version

Name:	image.png
Views:	0
Size:	62.8 KB
ID:	121628​Then from the submenu on the errors you can choose view list:
        Click image for larger version

Name:	image.png
Views:	0
Size:	20.9 KB
ID:	121629​Now go to the full form button:
        Attached Files

        Comment

        • jeffreysgrossman
          Senior Member
          • Jan 2025
          • 120

          #5
          This then allows you to choose all of the entries and under action choose export:

          and from there you get the file that you can use to send to the third party that do not already exist in ESPO.

          Again not totally sure thats what your looking for but it seems like it might be a way to achieve the results and get going while you get something better developed? Just a thought and hope it helps.

          Thank You,
          Jeff​​

          Comment

          • jeffreysgrossman
            Senior Member
            • Jan 2025
            • 120

            #6
            Then the list of all the imports are stored and can be found from the import results button
            Click image for larger version

Name:	image.png
Views:	0
Size:	9.3 KB
ID:	121633You get a list of all the previous import and the results of each
            Click image for larger version

Name:	image.png
Views:	0
Size:	47.3 KB
ID:	121634
            Click image for larger version

Name:	image.png
Views:	0
Size:	61.4 KB
ID:	121635

            Comment

            • jeffreysgrossman
              Senior Member
              • Jan 2025
              • 120

              #7
              To achieve the last requirement would require you to have a field in the entity that you could update using the manual field selection in the import process. The easiest being a date field that you populate with the date that your importing. This will allow you to then go to the list of that entity and build out a filter that looks for any record in that entity that does not have that import date. That is your list of records to give back to the third party that exist in ESPO but were not on the list.

              Creating a custom date field and populating each import with that date using the default feild section in the bottom:
              Click image for larger version

Name:	image.png
Views:	0
Size:	14.9 KB
ID:	121637

              I think that does all the features you listed. Its complicated and not elegant. But I do believe you could hit the ground running using the existing import feature and methods listed. Let me know what you think. If it wont work no issues. But maybe it will..

              Have a great day!

              Comment

              • JRender
                Junior Member
                • Mar 2025
                • 15

                #8
                Thank you for your input Jeff. I hadn't thought about using the import feature as a pseudo-cross reference tool. I'm going to have to spend some time thinking considering how difficult it would be to implement as such and whether that method would be better than any others that I can come up with.

                Comment

                Working...