How can I insert a specific row whose last column changes to the last day of every month?

[mifrahm]

I'm looking to copy a process I used to do in excel. I copy over cells E7:I121 from Sept Mock and paste this to the end of Mock Fx. Then I manually populate the last day of that month (in this example it would be 9/30/2020. The last step is to always add a row such as row 2 or row 119 or row 233 in the Mock Fx sheet. And match the last date of the month.

This is a monthly process that I am looking to eventually looking to convert into an app. I am just having trouble figuring out how to get the specific highlight rows in Mock Fx to come to the end of my workflow + have the last date of the month. I am attaching a sample workflow of where I am in this process and would appreciate any help. Thank you community!

[clmc9601]

Hi @mifrahm,

Does this help? I also updated some of your existing workflow to be more efficient.

 

[mifrahm]

@clmc9601 - thank you for the quick reply! Can you please attach a 2018 workflow by any chance? I am unable to open your copy due to my older version.

[clmc9601]

Hi @mifrahm,

I edited and reattached the zip to be compatible. Please let me know if it doesn't work-- sometimes editing yxzp doesn't work like I expect.

For the future, you can open yxmd files in a text editor like notepad and change the version compatibility yourself as seen in this screenshot 🙂

[mifrahm]

@clmc9601 - thank you for sharing this tip! At a quick glance, this looks like what I want. However, I will try it with the real data + more months, just to be sure & then mark this a solution, which will probably be the case :-).

Can you please explain your formula Multi-row formula, & what it is "doing"?

IF IsEmpty([Period End])
THEN [Row-1:Period End]
ELSE [Period End]
ENDIF

Also, does it matter if I manually configure on the join tool in container 24? 

[clmc9601]

Of course! Please, make sure it truly works before marking it as a solution. I try and build dynamic solutions that will work beyond the sample data given, so I appreciate any feedback if you discover it breaks at a certain point.

Absolutely. Here is the logic behind the multi-row expression:

Line 1: if [Period End] is empty - the only row it should be empty for is the USD for USD row

Line 2: then, copy the [Period End] date from the row above - since your data is sorted with the USD row at the bottom, it will apply the end date from the previous row to the USD for USD row

Line 3: if [Period End] is not empty, ignore this expression and move on unchanged with the current value for [Period End]

Line 4: closing syntax

I didn't include a join tool in Container 24. If you mean the union tool, sure you can manually configure it. However, I don't recommend this because it's not a very dynamic solution. That is to say, if your column names ever change, it will break or need to be readjusted. Instead, I would suggest leaving it set to auto-configure by name and only making changes in your original spreadsheet and the text input tool. But yes, it's technically fine to manually configure and a dynamic solution might be more difficult depending on your use case. 

[mifrahm]

@clmc9601  - appreciate the reply. Yes, I meant to say union tool.

Also for the append tool, it seems like it typically errors on more than 16 records Since we have it enabled to allow for all, is that going to cause any problems should I make this into an app down the line (slowing down/usage etc.?).

[clmc9601]

Great question 🙂 

Right now, it is appending a single row (the date) to a group of rows (the currencies). This is not creating any additional records. The slowness and heavy memory consumption are a function of creating additional records exponentially, which is what happens when you append a group of rows to a group of rows. As long as you only intend to append a single date at a time, it should perform just fine. If you plan to import multiple months' files at a time, a) that would be tricky to use the select records tool to parse the data, and b) it might be better to do some sort of join by record ID instead of append. When appending a single record, the append tool is simpler to use than a join. Converting the workflow to an app shouldn't affect performance. I hope this answers your question.