+8On this view,
I need to create groupings based on the Max Price and Listing Price.
The amount should match, but when I tried to do the grouping it did not show the correct numbers.
here's the current setup.
Here's the outcome.
The Max Price Should be $280k but why is it that the Listing Price of $285k was included on this group which supposedly was not?
Best answer by Arthur_Tutt
Hey @Imee_Lee ! I think I've got an alternate solution for you. Recorded a Loom Video here to walk you through step by step 🙂
Essentially this is an automation that will link your Hit List Clients to your Rejected Properties, based on the Property Listing Price, and their Min/Max Purchase Budget. I'll show you my example here:
*** REMEMBER TO UPDATE ALL TABLE / FIELD / VARIABLE NAMES TO MATCH YOURS ***
Sample Base Setup:
Desired Output - Properties are now grouped by Hit List Clients that land in price range.
Automation Setup:
Script Input Variables:
Script:
*** REMEMBER TO UPDATE ALL TABLE / FIELD / VARIABLE NAMES TO MATCH YOURS ***
// get property table
var propertyTable = base.getTable("Properties");
// get client table
var clientTable = base.getTable("Client Briefs");
var clientQuery = await clientTable.selectRecordsAsync({fields: ["Client", "Min Purchase", "Max Purchase"]})
var clientRecords = clientQuery.records;
// get input variables
var inputConfig = input.config();
// get properties input variables
var recordID = inputConfig.recordID;
var listingPrice = inputConfig.listingPrice;
var potentialClient = [];
for (var i = 0; i < clientRecords.length; i++) {
if (listingPrice >= clientRecords[i].getCellValue("Min Purchase") && listingPrice <= clientRecords[i].getCellValue("Max Purchase") ) {
potentialClient.push(clientRecords[i])
}
}
console.log(potentialClient)
// Update Potential Client Record
var updates = [{
"id": recordID,
fields: {
"Potential Client": potentialClient
}
}]
console.log(updates)
await propertyTable.updateRecordsAsync(updates);
Last thought : remember that the trigger of "Enters A View" will only trigger when there is a state change and a new property ENTERS the view. This means that any properties ALREADY in the rejected properties view will NOT trigger the automation. One way to solve this is to trigger them manually (like I did in the video). Or alternatively you could temporarily remove them from the view and re-add them. This will trigger the automation.
Let me know if this worked for you!!
+25Hi,
it's usual grouping work. Maybe you need some other function?. What is your desired output?
If you want to see records where Max Price=Listing Price, you should add formula field
Example: IF({Max Price}={Listing Price},'Prices are equal')
and then filter by this field. And then maybe group, if you need
+19Hey @Imee_Lee ! Is this a follow up from your previous post? I've been meaning to get back to you. I still need a bit of clarification on what you're trying to achieve here. For example, let say you have a rejected property with a Listing Price of $500k, do you then want an output of all the clients that that falls within their budget (say Min $300k to max $600k)? If so I can test around some automation ideas to make that happen.
I don't believe what you're proposing in your question will work. If a record has a Max Price of $280k and a Listing Price of $285k, those values will never be equal and can't be grouped together, so I'm trying to think of alternate ways to achieve your objective here 🙂
+8Hey @Imee_Lee ! Is this a follow up from your previous post? I've been meaning to get back to you. I still need a bit of clarification on what you're trying to achieve here. For example, let say you have a rejected property with a Listing Price of $500k, do you then want an output of all the clients that that falls within their budget (say Min $300k to max $600k)? If so I can test around some automation ideas to make that happen.
I don't believe what you're proposing in your question will work. If a record has a Max Price of $280k and a Listing Price of $285k, those values will never be equal and can't be grouped together, so I'm trying to think of alternate ways to achieve your objective here 🙂
Hello @Arthur_Tutt Thank you for following this up.
Yes it's related to the other post, I was able to use Junction to connect.
And my issue now is this if you could please check if it's possible
+19Hey @Imee_Lee ! I think I've got an alternate solution for you. Recorded a Loom Video here to walk you through step by step 🙂
Essentially this is an automation that will link your Hit List Clients to your Rejected Properties, based on the Property Listing Price, and their Min/Max Purchase Budget. I'll show you my example here:
*** REMEMBER TO UPDATE ALL TABLE / FIELD / VARIABLE NAMES TO MATCH YOURS ***
Sample Base Setup:
Desired Output - Properties are now grouped by Hit List Clients that land in price range.
Automation Setup:
Script Input Variables:
Script:
*** REMEMBER TO UPDATE ALL TABLE / FIELD / VARIABLE NAMES TO MATCH YOURS ***
// get property table
var propertyTable = base.getTable("Properties");
// get client table
var clientTable = base.getTable("Client Briefs");
var clientQuery = await clientTable.selectRecordsAsync({fields: ["Client", "Min Purchase", "Max Purchase"]})
var clientRecords = clientQuery.records;
// get input variables
var inputConfig = input.config();
// get properties input variables
var recordID = inputConfig.recordID;
var listingPrice = inputConfig.listingPrice;
var potentialClient = [];
for (var i = 0; i < clientRecords.length; i++) {
if (listingPrice >= clientRecords[i].getCellValue("Min Purchase") && listingPrice <= clientRecords[i].getCellValue("Max Purchase") ) {
potentialClient.push(clientRecords[i])
}
}
console.log(potentialClient)
// Update Potential Client Record
var updates = [{
"id": recordID,
fields: {
"Potential Client": potentialClient
}
}]
console.log(updates)
await propertyTable.updateRecordsAsync(updates);
Last thought : remember that the trigger of "Enters A View" will only trigger when there is a state change and a new property ENTERS the view. This means that any properties ALREADY in the rejected properties view will NOT trigger the automation. One way to solve this is to trigger them manually (like I did in the video). Or alternatively you could temporarily remove them from the view and re-add them. This will trigger the automation.
Let me know if this worked for you!!
+8Hey @Imee_Lee ! I think I've got an alternate solution for you. Recorded a Loom Video here to walk you through step by step 🙂
Essentially this is an automation that will link your Hit List Clients to your Rejected Properties, based on the Property Listing Price, and their Min/Max Purchase Budget. I'll show you my example here:
*** REMEMBER TO UPDATE ALL TABLE / FIELD / VARIABLE NAMES TO MATCH YOURS ***
Sample Base Setup:
Desired Output - Properties are now grouped by Hit List Clients that land in price range.
Automation Setup:
Script Input Variables:
Script:
*** REMEMBER TO UPDATE ALL TABLE / FIELD / VARIABLE NAMES TO MATCH YOURS ***
// get property table
var propertyTable = base.getTable("Properties");
// get client table
var clientTable = base.getTable("Client Briefs");
var clientQuery = await clientTable.selectRecordsAsync({fields: ["Client", "Min Purchase", "Max Purchase"]})
var clientRecords = clientQuery.records;
// get input variables
var inputConfig = input.config();
// get properties input variables
var recordID = inputConfig.recordID;
var listingPrice = inputConfig.listingPrice;
var potentialClient = [];
for (var i = 0; i < clientRecords.length; i++) {
if (listingPrice >= clientRecords[i].getCellValue("Min Purchase") && listingPrice <= clientRecords[i].getCellValue("Max Purchase") ) {
potentialClient.push(clientRecords[i])
}
}
console.log(potentialClient)
// Update Potential Client Record
var updates = [{
"id": recordID,
fields: {
"Potential Client": potentialClient
}
}]
console.log(updates)
await propertyTable.updateRecordsAsync(updates);
Last thought : remember that the trigger of "Enters A View" will only trigger when there is a state change and a new property ENTERS the view. This means that any properties ALREADY in the rejected properties view will NOT trigger the automation. One way to solve this is to trigger them manually (like I did in the video). Or alternatively you could temporarily remove them from the view and re-add them. This will trigger the automation.
Let me know if this worked for you!!
Hi @Arthur_Tutt - I sincerely appreciate the time and effort you have invested in creating this sample database, along with the automations and script.🙌
Your dedication to understanding the core of the issue has been incredibly helpful, and I am genuinely thankful for the level of detail and expertise you've applied. 👏🙌
Your work has significantly contributed to progressing my project, and the effectiveness of the automations you've developed is truly commendable.🤓
I did come across one aspect that seems to require further attention. I've recorded a brief video to illustrate this specific point: I believe addressing this will enhance the functionality even further. 🤞
Thank you once again for your valuable contributions and for being so receptive to feedback. I look forward to your thoughts on the issue highlighted in the video.
+19Hey @Imee_Lee Great job implementing that solution so fast! I know it was a little complicated but you nailed it 😊.
And thanks for the follow up vid, I've got 2 thoughts for you:
Firstly, to achieve your objective, on line 19 of the script all you need to do is change ">=" to "==", which should look like:
if (listingPrice == clientRecords[i]...
HOWEVER - a word of caution. Using this method means that many properties will NEVER have an associated client. What I mean is that if the listing price is $197,000, no client will have their max budget match exactly that, they'd have $200,000.
So as long as you're cool that many properties will never be associated with a client, then fine, proceed. But in my opinion you'll be losing many connection opportunities (which is why I set to match in between min and max budgets). But hey it's your business, do as you like, I just want to give you the facts 🙂
+8Hey @Imee_Lee Great job implementing that solution so fast! I know it was a little complicated but you nailed it 😊.
And thanks for the follow up vid, I've got 2 thoughts for you:
Firstly, to achieve your objective, on line 19 of the script all you need to do is change ">=" to "==", which should look like:
if (listingPrice == clientRecords[i]...
HOWEVER - a word of caution. Using this method means that many properties will NEVER have an associated client. What I mean is that if the listing price is $197,000, no client will have their max budget match exactly that, they'd have $200,000.
So as long as you're cool that many properties will never be associated with a client, then fine, proceed. But in my opinion you'll be losing many connection opportunities (which is why I set to match in between min and max budgets). But hey it's your business, do as you like, I just want to give you the facts 🙂
Thank you @Arthur_Tutt ! It worked!
But I think this is the last one,
The client that I need to gather should only be coming from specific "status".
For this current set up, it is getting all of the clients that have all of our status.
Do you think this is possible?
+19Hah @Imee_Lee so close 😂
First thought that comes to mind: the easiest way to achieve this is to add a condition to your Hit List view (or make a new view to include it). If your Hit List view filters only those clients with Active Search, then the automation should only include those clients (and not Settled), because it only looks for data in the Hit List view. Make sense?
+8Hah @Imee_Lee so close 😂
First thought that comes to mind: the easiest way to achieve this is to add a condition to your Hit List view (or make a new view to include it). If your Hit List view filters only those clients with Active Search, then the automation should only include those clients (and not Settled), because it only looks for data in the Hit List view. Make sense?
Kinda difficult to picture out @Arthur_Tutt sorry!
Is it possible to give me a sample how to set that condition? please 😅
+19@Imee_Lee For sure I got you 😉
So on the Client Table I added new field called 'status'.
Then on Client Hit List View I add filter to filter by status:
Make sense?
+19@Imee_Lee WAIT! I think I just figured out the problem. In the script I was quarrying the entire Client Table, instead of just The Hit List View. You'll need to update lines 5-7 of the script w these lines (adjusting Table / Field / View names to match yours as usual)
// get client table
var clientTable = base.getTable("Client Briefs");
var clientHitList = clientTable.getView("Client Hit List");
var clientQuery = await clientHitList.selectRecordsAsync({fields: ["Client", "Min Purchase", "Max Purchase"]})
+8@Imee_Lee WAIT! I think I just figured out the problem. In the script I was quarrying the entire Client Table, instead of just The Hit List View. You'll need to update lines 5-7 of the script w these lines (adjusting Table / Field / View names to match yours as usual)
// get client table
var clientTable = base.getTable("Client Briefs");
var clientHitList = clientTable.getView("Client Hit List");
var clientQuery = await clientHitList.selectRecordsAsync({fields: ["Client", "Min Purchase", "Max Purchase"]})
@Arthur_Tutt you've been incredibly helpful! 🌟
The "filter" feature was already in place, so there was no need for me to do it.
The adjustments you made to the script were spot on! 💪
Immense gratitude for your assistance! You truly turned my day around! Thank you so much!! 🙌 🙌
+19@Imee_Lee yessssssssssss! Wow this one was a nailbiter, but we made it!! Glad could be of assistance and make it work for you. And thank you for being an action taker and implementing it and making it happen. Best 😎
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.