Multi Row If Statement - Help

[sslattery17]

I used the below if statement to fill down the "Primary Name Field and I will use the same for the 3 date fields and other columns; however, before I can do that; I need to fill in the empty date field with a generic holder like 0000-00-00 or no data.  I am not sure how to accomplish this without it also filling in all empty rows unless I can configure mutli-step if statement for example but cant get it to work properly.  In order to use name field; would need date field as step one then I can fill down name.  I know order makes a difference. Any assistance would be helpful.

Fill empty date field:

if !isempty([Primary Name]) and if isempty([Lease End Date])

then 0000-00-00

else [Lease End Date]

Fill down empty rows:

if isempty([Primary Name])
then [Row-1:Primary Name]
else [Primary Name]
endif

Primary Name         Move-In Date        Lease Begin Date     Lease End Date    Charge Code

Alindo DosReis        2019-04-27           2024-05-01               2025-04-30            Rent

Alindo DosReis                                                                                                     Amenity Rent

Alindo DosReis                                                                                                     Garage Fee

David Nordquist       2020-02-01           2025-01-01                                             Amenity Rent 

David Nordquist                                                                                                    Month to Month

David Nordquist                                                                                                    Concession-Part-time Employee

Gregory Daniel        2020-03-01            2025-04-01               2026-03-31           Amenity Rent

[CoG]

What does your input data look like? How do you know that records without a value for [Primary Name] belong to that individual? Lastly, why do you need to add the placeholder data at all. It seems like getting the name added matters much more?

In regards to my last question, it may be better adding a Group ID field first (depending on your dataset the Multi-Row Formula Tool may be required), which you can then use to keep track of which records belong together for all other portions of the workflow. 

[sslattery17]

@CoG

I figured out the multi row formula to solve the missing date categories for each tenant.  Yes you are correct; name matters, unit matters, etc.  I attached the data set that I bring in.  But I still need help figuring out a multi row formula for a different piece of this workflow.

If you look at the first tab; end of data; there is a row that starts "Future Resident Details" and when the next sheet is brought in the next data row starts with "Rent Roll Valencedocs"; I need everything between those removed and not sure of the best way to do this. 

I have not figured out the best way to assign those units/tenants as Future so I can filter them out.  If I leave this to the end.  Future tenants show up in my final output also attached.  Look at Belmont; 3rd tab Future Tenant 032-03 shows on final report.  Not all Future tenants have 0 rent; so I cant filter that as final result.  The Unit type is also the same as Current Tenants in many cases.

Any direction is helpful.  Thank you much.

[sslattery17]

@CoG 

I exported the workflow package.  There is also a part 2 which is the final report for each property that takes the file generated by part 1.  

[sslattery17]

@CoGand @binuacs 

Looking for assistance on this issue.  I was trying to build either a regex or other to remove rows between two key words.  I exported the workflow and the data set attached to the post.   I figured out the first part of the post issue of filling in the empty date categories now trying to remove all data rows between "Future Resident Details" and "Rent Roll Valencedocs".  I have been using regex101 to help build and test but I cant seem to get there.  I have tried trim and replace and still only removes parts of data and not all data between those parameters.

Thank you Shannon

[binuacs]

@sslattery17 , your requirement is not clear. can you provide an input file and expected output file for better understanding

[sslattery17]

@binuacs 

I am not sure if you can access the entire message thread but I attached the workflow package and results.  I am trying to remove any data between the two texts " Future Resident Details" and "Rent Roll Valencedocs" see screen shot.  This type of data appears for each sheet when it is brought into the workflow.  Because the data under Future Resident Details can match a unit # from current (all units above FRD; I cant filter out duplicate unit #s.  There is no specific key to filter out the data under Future Resident Details and before the next set of data begins Rent Roll Valenancedocs.

If the data between these two words are removed; then I can filter out the two words later in the workflow.  I have even tried to assign an F front of the Unit # for Future Tenants but could not figure that out to then Filter them out.

Future Resident Details

Bldg-Unit Unit Type SQFT Unit Status Resident Move-In Lease Start Lease End Market Rent Ledger Charge Code Transaction ID Post Date Actual Charges Scheduled Charges Balance Deposit Held

032-032-03 JR3 420.00 Notice Rented Corey, James 5/1/2025 5/1/2025 4/30/2026 1,440.00 Resident Rent 0.00 1,440.00 0.00 1,440.00 

Charge Total: 0.00 1,440.00

Belmont West Total: 420.00 1,440.00 0.00 1,440.00 0.00 1,440.00 

Rent Roll Valencedocs

[binuacs]

@sslattery17 the screen shot is not uploaded properly. do you want to remove any rows after the text "Future Resident Details" (below screen shot from  the sheet "Bartons Mill Apartments" for example)

[sslattery17]

Yes that is correct. 

[sslattery17]

@binuacs 

Yes that is correct.  Any text after "Future Resident Details".

When you bring all the sheets to the workflow; "Rent Roll Valencedocs is the 1st or Second Row before the property name.  When they are all in view regardless of which order; FDR data text is between FDR and Rent Roll Valencedocs.  Which is just before the next properties current data.

This was my logic to remove data between the two parameters....