Set a lookup field based on a search

[mrjdatta]

In my scenario, there are 3 entities. Property, Tenancy and Billing Event. Tenancy is a child of Property. There may be many Tenancy records, but only one will be Active. The Billing Event has a lookup to both Property and Tenancy.

What I'm trying to do is this: The user will create a Billing Event record and populate the Property lookup field. I want the workflow rule to search for the Active Tenancy record and automatically populate the Tenancy lookup. I hope this is clear. Here's an ERD to help visualize.


The workflow rule that I created is this:
Target Entity: Billing Event
Trigger Type: After record created
Conditions: Tenancy is empty
Actions: Update Target Record » Billing Event
Actions formula: tenancy=record\findOne('Tenancy', 'leaseStartDate', 'desc', 'property=', property, 'status=', 'Active')

Is what I'm trying to do possible? And if so, what am I doing wrong? (it's not working)

[mrjdatta]

I figured it out on my own. I don't see a way to delete this post, so I'll go ahead and share my solution.

I was missing the "Id"s for the lookup fields. Final formula:
tenancyId=record\findOne('Tenancy', 'leaseStartDate', 'desc', 'propertyId=', propertyId, 'status=', 'Active');

[espcrm]

Please do not try to delete post! This is very useful for other in the future (or right now). Nice drawing by the way.

I'm kinda interested in this topic you have, good to see more type of these usage. Have you install the Real Estate extension yet? It quite good.

Rather interested in knowing a bit more about your setup.

---

From what you are trying to achieved, the workflow purpose to is to automatic link a Tenant (Active) (Contacts) to the property right? Save the trouble of having to manually pick and choose the Tenant?

[mrjdatta]

I looked at the Real Estate extension very briefly. I felt that my requirements are a little more custom and I wanted to build the entity schema from scratch for that reason.

Happy to tell you more about my setup. To be honest, I'm new to EspoCRM but I have lots of experience with other platforms. So far I'm loving it!

My setup is intended for a group of Property Managers. The diagram that I shared is only the custom entities and I kept it simple for the purpose of my question. However there are plenty of relationships to standard entities. Ex: the Property has an Owner (Contact), the Tenancy has Tenants (Contact), the Property has a Builder (Account), etc....

The idea behind the Billing Event is to track any monetary transactions. For example: receiving rent, collecting management fees, paying the owner, additional costs (repairs or maintenance), etc... The reason why it's connected to both the Property and the Tenancy is because tenants come and go. We need to be able to see all transactions for a property regardless of tenants (to report back to the owner). We also want to be able to report on transactions specific to a tenant (create rent receipts, expose data to a tenant portal, etc...) So yes you're right that the purpose is for ease of data entry. If I'm entering a Billing Event, it's very very likely that it's related to the CURRENT tenant.

Another neat thing that I did if you're curious was streamlining the data entry when rent is received. Imagine on the first of the month I received rent from 20 different tenants. I would have to enter the billing event manually 20 times.
Solution: I created an Enum field on the Tenancy entity called Rent Received. The default value is Pending, with options for Yes and No. I then created a workflow for when it's changed to Yes to create a Billing Event (set the Type, Date, Property, Tenancy automatically, and copy the Amount from the Rent field on the Tenancy). The workflow also resets the "Rent Received" field back to Pending.
Result: 1) The user opens the "Active Tenancy" list view 2) Check off all the records for which he received rent 3) Mass Update the Rent Received field to Yes 4) System creates the Billing Event records for every Tenancy and the field is reset so that it's ready for the next time rent is received. Way less clicks!

Again I'm very new to EspoCRM and I'm having a blast solving individual problems and learning along the way. One of my next major hurdles will be generating a PDF with very specific formatting. My gut tells me that it will be beyond the standard capabilities of print to PDF. I will have to generate things like Tenant Agreement, Notice to end a tenancy, etc... These are government documents and I can't just create my own.

[espcrm]

The Real Estate extension make it quicker to get started I suppose, I like how it does the property name and do a few linking relationship from there. From that I expand it to fit our need a little more and it slowly becoming better.

With your Owner (Contact) and your Tenants. Both you use Contact right? Or did you create a (New Custom) Contact entity for Owner and Tenant.
I assume you would create two One to Many or Many to Many relationship from Property to Contact. One relationship to Property-Owner and one to Property-Tenant.

How are you doing the Billing Event, did you bought the Sale Pack? Since it look like you got the Advanced Pack. This is probably the most troublesome entity that I haven't even bothered with, especially with calculation and formula? Do you use formula for these?

With PDF, if you planning to make it look good, you will spend a lot of time. The current PDF system is very limited and restrictive in the CSS. The editing tools is also very restrictive as well, you can make certain modification to the layout. For example the Width and Height of the table is always "auto", you need to use Code <> view to change it. I end up just using a HTML editor software/online. Then there is also a font issue, it seem that you can only choose one type of font. Changing the size of font doesn't seem to match the visual feedback you are seeing either. I wanted to generate those type of documents too but you just cant unless it a very simple layout one (letter). Any sort of form with multiple column and more then Paragraph type of layout will be difficult.

But who know, you manage to get this far so you may be able to solve the issue. I like some of your idea, will see if I can implemented base on the information.

[mrjdatta]

Regarding your question on the use of Contact, you're correct. I made sure to use the standard entities as they are intended. So if you're a person, you're a Contact. if you're a company, you're an Account. And you're close with the relationships. They're both many-to-many, but the relationship to the Tenant is on the Tenancy entity, not the Property. Remember that for the entire life of a Property, you will have tenants come and go. We want to maintain the history of all rent collected and issues related to each tenancy. If I simply had the tenant relationship on the Property, I would have to replace the value when a new tenant moves in. I attached an updated ERD to help visualize that. Past Tenancies have a status of "Inactive", but the history is still there.

Regarding the Sales Pack, I don't think it's a good fit with how we intend on using it today. We're not selling "products" per se. There's no concept of a sales order, or invoice. The Billing Event is a means to track anything monetary that happened related to the Property (rent, fees, repairs). That way we can report at the end of the month or year in a simple GL kind of way. I haven't had to do any formulas yet. Each record represents a description, transaction date, and amount. That's it.

With PDF, thanks for that feedback. What you said is what I was assuming. I haven't spent any time on this yet as I want to make sure I have my requirements 100% clear before wasting any time. But what I was thinking was using something like TCPDF (https://tcpdf.org/). It seems to be quite flexible, but I'll have to spend the time coding it. So far I've managed to get an incredible amount of customization without having to touch a line of code. And that really excites me!

[jakov]

Hi mrjdatta & espocrm & item

Your conversation and suggestions are very helpfull for a novice like I am. My setup would be intended for Property Agents (buying/selling Property and Rental). The idea behind my Project is to support Agents trought whlole business process via workflow or Dynamic Forms.

Main Project goals are:
0. to automate all task Agents has in each Property/Rent business process
1. to cover all documentation in digital Form with eSigned support (can not get to work open source extension -when tenecy esign is includet on Layout system froze...
2. to use data entered for main Contact (who paid what, from what source and when:

2a. Custom Entity to track monetary transaction like you to @ mrjdata - if Security Deposit=true, then -invoices, - tax payments, - line of credit payment, - other payments= endd balance, that must be then sent on seller Bank
2b. To track when/if security Deposit is on our Bank Account and if all outgoing invoices are paid (we will get ths data in xml outside Espocrm system -wil converted it/import in tabl
2c. to make some event/workflow to create all sales order/invoice for buyer/buyers and seler7seles in propotion entered in Property Entity (a PropertyOwnership Entity is a issue)
2d. to create payment orders -create xml file and save it to target folder

3. To make Property sattelmet for Agents, based on Invoice provision (dinamic sale scale) & to create Agents Invoice based on Property Sattelment
4. to be able to create controlling reports out of one table via Excel/Pivot

My first chalange is to prepare strong Fundations - Entity and there relationschips (picture below) & how to setup whole Logic and how to automatize Agens workflow Task.

Things to consider in Real Estate business:
-Property can link to one or many buyers and sellers
-Property ownership is important to consider -invoice & Money Transactions
-Agents cooperations on eash Property - based Value for Property settelment (agent A 60%, Agent B 40%)
-standard 2 or 3 Templates for Sales orders/Invoicing