Need an assist on a complex Xero entry, if it is even possible with Make

[Insane_Squirrel]

I am trying to set up an integration from Excel to Data-store to Xero to record a Manual Journal Entry.

The result I am looking for is a Xero Manual Journal Entry where Line 1 (or the last line) is the total sum of all other lines. The lines would be coming from data-store where I have uploaded all the values for each period from Excel. The Xero Journal Entry would need to create a new line for each value with the description being the name of the person.

The data-store has a name with a value for each month (Jan, Feb, Mar…), this value would be the amount appearing on each line. Within the data-store it is divided by Quarter the contracts were signed. Each Quarter will have a varying amount of persons and over time as the contracts end, the people will drop off the list (updated to $0), so I cannot make a static entry that just maps each bundle to the lines.

I suspect I need an aggregator between the data-store and Xero Manual Journal Entry. I am also confused by the “map” line as I can’t figure out a way to get this to do what I want.

I have been working on this for the past 3 months or so, with varying degrees of progress but I am finally breaking down to ask for help.

Any assistance would be appreciated.

[samliew]

Welcome to the Make community!

1. Screenshots of module fields and filters

Please share screenshots of relevant module fields and filters in question? It would really help other community members to see what you’re looking at.

You can upload images here using the Upload icon in the text editor:

2. Scenario blueprint

Please export the scenario blueprint file to allow others to view the mappings and settings. At the bottom of the scenario editor, you can click on the three dots to find the Export Blueprint menu item.

^{(Note: Exporting your scenario will not include private information or keys to your connections)}

Uploading it here will look like this:

blueprint.json (12.3 KB)

3. And most importantly, Output bundles

Please provide the output bundles of the modules by running the scenario, then click the white speech bubble on the top-right of each module and select “Download output bundles”.

A.

Save the bundle contents in your text editor as a bundle.txt file, and upload it here into this discussion thread.

Uploading it here will look like this:

bundle.txt (12.3 KB)

B.

If you are unable to upload files on this forum, alternatively you can paste the formatted output bundle in this manner:

• Either add three backticks ``` before and after the code, like this:

  ```
  <sup>input/output bundle content goes here</sup>
  ```

• Or use the format code button in the editor:
  

Providing the output bundles will allow others to replicate what is going on in the scenario even if they do not use the external service.

Following these steps will allow others to assist you here. Thanks!

samliew – request private consultation

Join the unofficial Make Discord server to chat with us!

[Insane_Squirrel]

Hello Samliew,

Sorry for not getting back to you immediately, but something bugged out on my end.

As I am dealing with live data and sensitive financial data I will be posting some “edited” screenshots to illustrate the desired end result. Hopefully explaining everything with context.

The Scenario

Here you can see the data being pulled from the datastore that was uploaded with a different scenario. This data is correct and the other scenarios are working without issue.

There are 116 bundles currently, but will be increasing into the 1000s eventually. These are subdivided into Company and Quarter it was posted (Key), as Xero has a limited number of lines that can be entered into a Journal Entry, this will need to be divided into separate entries.

This part is less of an issue compared to the next screenshot.

With this screenshot, I am trying to input into the “Map” Section of the Xero Manual Journal Entry module a JSON to post the Journal Lines.

I tried using the “input JSON” from Xero’s API’s documentation, but that doesn’t seem to work.

This results in an output error of:

This is one of many errors that have occurred as I have been changing and trying new things. But it just doesn’t seem to like even my static JSON (without any “variable functions”).

The Hurtles
Within the Journal Lines, I need the system to spit out a separate line for each bundle’s specific month’s output for interest as the amount. The description for that line would be the Lessee’s Name and Vehicle.
After all the bundles have run, a last line would sum all the interest amounts.

All the other areas of the Manual Journal Module seem to be fairly easily mapped or changed manually as needed.

So to summarize, not only do I have issues with just making the “Map” in the Journal Entries function, but I also need to complicate that function by having it create a separate line for each bundle, dependent on the filtering of Months and Destination Accounts (I can figure this out on the filters), but meaning this will be 1-30 lines depending on that filtration.

My Questions
What code is supposed to be “mapped” into the Journal Entry lines? I would have assumed it was the JSON API from Xero’s API. Found Here

Do I need an aggregator in between the Datastore and Xero? As I need 1 Journal Entry per batch not an entry per bundle, I believe this is done with an Aggregator converting them to an Array. Does that make sense?

Can this even be done with Make? Or is the “repeat function” within the JSON Xero Mapping too difficult to handle?

If all these questions seem basic, I apologize as I am an accountant, not a programmer.
Best I can do is Excel formulas and read code these days.

Any assistance is much appreciated.

[TheWolfyAmongUs]

I can’t see the Xero module interface myself, but it looks like you may need to parse the JSON into an array and then pass it to the module. Your JSON looks right compared to the API doco, but the module accepts arrays maybe?
Can you post the interface of the module before you map stuff in?

[AutoPilots]

Hi @Insane_Squirrel, you can work with an Array Aggregator between the Datastore and the Xero-Manual journal modules to map the line items of the manual journal.
In the aggregator, use the Xero module as the target module so Make knows which fields have to mapped. See below for a simplified version.

mapping aggregator:

mapping in Xero:

Since you want 1 journal per bundle, I think you have to add an iterator on the interest array from the Datastore first, and use this iterator as the source module in the array aggregator. This way you will process the Interest amounts as line items. You probably have to tweak this a bit to get the data in the way you want to have it…
Hope this helps.