Show Properties based on Customer Preferences

  • 0
  • 2
  • Question
  • Updated 2 years ago
  • In Progress
I have a properties table which has number of bedrooms, price, country etc
I have a customers table with preferred Bedrooms, Price and Country etc.

How do I match up the properties that fit ALL criteria with the preferred fields in the Customer Table.
Photo of Mick Graham

Mick Graham

  • 330 Points 250 badge 2x thumb

Posted 2 years ago

  • 0
  • 2
Well, there are a variety of different ways to accomplish this, depending on the output you desire.

1. You could create a button on the customers table that would open a report of matching properties based on the customer preferences. (They would only be viewable when clicking the button)

2. You could go to the properties table and use dynamic filters or advanced search to create a similar report.

3. You could create a many to many table (customer properties) that would allow you to link all matching properties to a customer and from there track activity against each customer property. (this would allow you to see their list while looking at a customer record. You might want a button that would go find all the properties and create the child records automagically if you go with this way. 

Do any of these sound better than another? What is the specific output you are looking for?
I was looking to have an embedded report on the customer form that contains the properties that match the criteria in the customers preferences.

Bedrooms in properties >= customers preferences
And
Price in properties <= customers max price

Etc.. .
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
I don't think that it can be done with an embedded report. It will take a button push at least, although e results after the button push could be on the customer preferences record.
Photo of Mick Graham

Mick Graham

  • 330 Points 250 badge 2x thumb
I was trying to do with a filtered report but I couldn't work out how to get it to search through all the properties.   It seems like it should be something simple but its not?
Photo of Mick Graham

Mick Graham

  • 330 Points 250 badge 2x thumb
I created a relationship between customers and properties and passed up the customer preferred fields up to properties.  Then I created a report in properties with Ask User to select the customer and then another filter to show properties where bedrooms >= to preferred bedrooms.   Still doesnt work ???
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
There is not a way to simply make a relationship between Customers and properties as you have no natural connection that a property belongs to a single customer.

The lowest Tech solution is to create an <ask the user> report which is waiting for the answer to the number of bedrooms  >= <ask the user> and the price is <= <ask the user>.

Then create a URL formula button (probably check the box to open in a new window) on the customer preference record like this.

URLRoot() & "db/" & [_DBID_my properties _Table] & "?a=q&qid=xx&nv=2&v0=" & totext([# of bedrooms]) & "&v1=" & totext([Price])

So in English that says run the ask the user report qid of xx and  and there are going to be 2 variables - ie questions  (nv=2) and the answer to the first variable v0 is the # of bedrooms and the answer to the 2nd question v1 is the price.

So the button will run the report and answer its own questions and display the result.
Photo of Mick Graham

Mick Graham

  • 330 Points 250 badge 2x thumb
Ill have a go at that, thanks
Photo of Mick Graham

Mick Graham

  • 330 Points 250 badge 2x thumb
Why would I need an ask the user for bedrooms and price?
The customer table contains fields called Preferred Min Bedrooms and Preferred Max Price.

If my report has these already then should it not just pick them up from the customers table and compare them against the corresponding fields in the Property Table?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
The way that the report "picks them up" is by running the report. To run the report you need to push the button.  There is not a way to have a Report Link "report" pick up the values from the customer record without pushing a button.  i have another solution which would display the results on the Customer record, but you would still need to push a button.
If we can bypass the ask the user, I have no aversion to pressing a button to get the report.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
That is why i gave you the code to make a button which will run the report, answer it's own question automatically,  and display the result.  The user does not see the <ask the user> question being asked or answered.
Photo of Mick Graham

Mick Graham

  • 330 Points 250 badge 2x thumb
What I don't understand is, How can it compare the properties: # of Bedrooms with the Customers: Preferred Bedrooms, with that code.   I'm kindof missing it?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
Mick,
Did you try the making the report and the button?  I'm not understanding what you are asking here.  My solution is low tech and native.  There may be fancier solutions which require developer assistance, but what i have offered first is just a down and dirty click a native button and get the query results.  The button takes the Customers preferences and supplies them into a report as a filter and runs the report.
Photo of Mick Graham

Mick Graham

  • 330 Points 250 badge 2x thumb
URLRoot() & "db/" & [_DBID_PROPERTIES] & "?a=q&qid=13&nv=2&v0>=" & ToText([Preferred Bedrooms]) & "&v1<=" & ToText([Preferred Max Price])

Note...  I used >= for bedrooms and <= for max price.

But the link breaks at the max price.  Its a currency field ?
a=q&qid=13&nv=2&v0>=4&v1<=300000

otherwise its picking up the beds and max price correctly.
Photo of Mick Graham

Mick Graham

  • 330 Points 250 badge 2x thumb
URLRoot() & "db/" & [_DBID_PROPERTIES] & "?a=q&qid=13&nv=2&v0>=" & ToText([Preferred Bedrooms]) & "&v1<=" & ToText([Preferred Max Price])

Note...  I used >= for bedrooms and <= for max price.

But the link breaks at the max price.  Its a currency field ?
a=q&qid=13&nv=2&v0>=4&v1<=300000

otherwise its picking up the beds and max price correctly.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
Mick, you are overthinking this.
Please do not change my formula.

Did you make the report that i suggested?

1. Make the report which has the <ask the user> questions as if you were going to do these inquires manually.

2. Then use the formula i suggested.  Don't corrupt it with >=  . The report itself will do that for you.

Once you have this working a light bulb will turn on in your head.  We are not there yet, but close to seeing that light bulb light up bright.
Photo of Mick Graham

Mick Graham

  • 330 Points 250 badge 2x thumb
It was the Report.  I had "=" Ask_User

doh!

I've learned something new...  brilliant Mark!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
So success??  It's all working?  Hurray!
Photo of Mick Graham

Mick Graham

  • 330 Points 250 badge 2x thumb
Hi Mark, Is there a way to expand this formula to work with a Multi Select Text Field?

The v3 only works if there is 1 selection in the field.  I have types, Villa, Townhouse, etc.
In the report I have type Contains ask_user.

URLRoot() & "db/" & [_DBID_PROPERTIES] & "?a=q&qid=16&nv=4&v0=" & ToText([Preferred Bedrooms]) & "&v1=" & ToText([Preferred Max Price])& "&v2=" & [Preferred Region]& "&v3=" & ToText([Preferred Type])
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
The issue is not with the formula, really.  The issue is with the report.

But before I can answer, does the Customer always select at least 1 choice from that Multi select field, or is it sometimes blank.
Photo of Mick Graham

Mick Graham

  • 330 Points 250 badge 2x thumb
The field isn't forced so it could be empty.
I guess that makes things more complicated ?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
I have to do some real work now, but I will post back later.  Basically, you will need to parse out the multi select field into its separate selections and feed the separate selections into the report, since a Property, presumably can only be 1 type, right. Like a Property  can not be both a Villa and a Townhouse, right?
Photo of Mick Graham

Mick Graham

  • 330 Points 250 badge 2x thumb
Yes, properties are only 1 thing :)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
Mick,

I suggest that the simplest way is to make two reports.  
Make the button itself have an IF statement in it to decide which report to use.

IF the Customer has left the  multi select field blank, then you know who to do that.

For the other report which does depend on the a match for that field, you will need ot parse out the multi select field into parts.

for example

the field for [Multi select 1] would be

Part(ToText([multi select field]), 1, ";")

then you would have a similar field for each possible part of that field, based on how many possible selections a Customer could make.

Then your 2nd report would have additional OR filters like

[type] equals <ask the user>
or
[type] equals <ask the user>

say 6 times of you had six choices.

So the button would decide which report to call based on if the user did or ddid not enter a selection for the type of property in that multiselect field. 
Photo of Mick Graham

Mick Graham

  • 330 Points 250 badge 2x thumb
OK, that looks really interesting.
I'll do that later today and let you know how it goes.
thanks Mark.
Photo of Mick Graham

Mick Graham

  • 330 Points 250 badge 2x thumb
It runs the new report and picks up the first option but ignores the next.
it picks up all the Townhouses but Not Villas.

I have AND
Type = ask user
or Type = ask user
etc 8 times for each of the options.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
Does the report work if you run it manually?

I would need to see the complete set of report filters to know if you have them properly nested.  You can attach a screen shot.

Then I would need to see the URL formula
Photo of Mick Graham

Mick Graham

  • 330 Points 250 badge 2x thumb
This is what the report is looking for when I have Apartment & Townhouse in the Type field and its now not finding anything:

Beds is greater than or equal to '3' AND Price is less than or equal to '€700,000' AND State/Region is 'Alicante' AND (Type is 'Apartment ' OR Type is ' Townhouse ' OR Type is ' Villa' OR Type is '' OR Type is '' OR Type is '' OR Type is '' OR Type is '' )

This is the Formula:
URLRoot() & "db/" & [_DBID_PROPERTIES] & "?a=q&qid=18&nv=11\n&v0=" & ToText([Preferred Bedrooms]) & "\n&v1=" & ToText([Preferred Max Price])& "\n&v2=" & [Preferred Region]& "\n&v3=" & Part(ToText([Preferred Type]), 1, ";")& "\n&v4=" & Part(ToText([Preferred Type]), 2, ";")& "\n&v5=" & Part(ToText([Preferred Type]), 3, ";")& "\n&v6=" & Part(ToText([Preferred Type]), 4, ";")& "\n&v7=" & Part(ToText([Preferred Type]), 5, ";")& "\n&v8=" & Part(ToText([Preferred Type]), 6, ";")& "\n&v9=" & Part(ToText([Preferred Type]), 7, ";")& "\n&v10=" & Part(ToText([Preferred Type]), 8, ";")
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
I see now that when we parse out the pieces of the multi select field, the part function will have added some extra spaces.

So we will need to trim them out.

So where is says 
Part(ToText([Preferred Type]), 3, ";")

 you will need to have 
Trim(Part(ToText([Preferred Type]), 3, ";"))
(Edited)
Photo of Mick Graham

Mick Graham

  • 330 Points 250 badge 2x thumb
Ho Ho!!!
Thats a great trick to keep up the sleeve LOL!
Marvellous Mark!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
That is great that its working.  Thx for letting us know.
Mick,

If you would like help with the correct / not low tech way of actually solving this the way you've described, contact me via the info in my profile. I'll see if I can send you an example solution in a bit.
Photo of Neal

Neal

  • 70 Points
Mick,

I have created prototype screenshots.

Following is for Customer Preferences - each record has link to show properties based upon preferences and clicking the link should show properties as per preferences.



Following is the list of properties.


Following is the list of properties that would be shown to  customer1.


Following is the list of properties for customer2.



Following is the list of properties for customer3.



Please send me email to nealpatil @ gmx.com if you are interested then I can build prototype.

Thanks,
Neal
This looks like it would do the trick
Hi Mick,

Here is a video showing a prototype of the solution I think you might be looking for:

http://cirrusops.screencasthost.com/watch/cbfOb7X2sQ