Discussions

Expand all | Collapse all

Show Properties based on Customer Preferences

Mick Graham04-26-2017 12:23

QuickBaseCoach Dev./Training04-26-2017 23:55

Mick Graham04-27-2017 22:34

Michael Michael04-28-2017 17:56

QuickBaseCoach Dev./Training04-28-2017 18:28

Michael Michael04-26-2017 16:35

  • 1.  Show Properties based on Customer Preferences

    Posted 04-25-2017 22:09
    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.


  • 2.  RE: Show Properties based on Customer Preferences

    Bronze
    Contributor
    Posted 04-25-2017 22:18
    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?


  • 3.  RE: Show Properties based on Customer Preferences

    Posted 04-25-2017 23:44
    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.. .


  • 4.  RE: Show Properties based on Customer Preferences

    Posted 04-26-2017 11:03
    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.


  • 5.  RE: Show Properties based on Customer Preferences

    Posted 04-26-2017 11:43
    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?


  • 6.  RE: Show Properties based on Customer Preferences

    Posted 04-26-2017 12:00
    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 ???


  • 7.  RE: Show Properties based on Customer Preferences

    Posted 04-26-2017 12:09
    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.


  • 8.  RE: Show Properties based on Customer Preferences

    Posted 04-26-2017 12:23
    Ill have a go at that, thanks


  • 9.  RE: Show Properties based on Customer Preferences

    Posted 04-26-2017 12:45
    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?


  • 10.  RE: Show Properties based on Customer Preferences

    Posted 04-26-2017 13:03
    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.


  • 11.  RE: Show Properties based on Customer Preferences

    Posted 04-26-2017 13:45
    If we can bypass the ask the user, I have no aversion to pressing a button to get the report.


  • 12.  RE: Show Properties based on Customer Preferences

    Posted 04-26-2017 14:01
    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.


  • 13.  RE: Show Properties based on Customer Preferences

    Posted 04-26-2017 16:20
    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?


  • 14.  RE: Show Properties based on Customer Preferences

    Posted 04-26-2017 17:39
    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.


  • 15.  RE: Show Properties based on Customer Preferences

    Posted 04-26-2017 23:26
    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">https://iglcloud.quickbase.com/db/bmnyuxj8n?a=q&qid=13&nv=2&v0%3E=4&v1">a=q&qid=13&nv=2&v0>=4&v1<=300000

    otherwise its picking up the beds and max price correctly.


  • 16.  RE: Show Properties based on Customer Preferences

    Posted 04-26-2017 23:26
    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">https://iglcloud.quickbase.com/db/bmnyuxj8n?a=q&qid=13&nv=2&v0%3E=4&v1">a=q&qid=13&nv=2&v0>=4&v1<=300000

    otherwise its picking up the beds and max price correctly.


  • 17.  RE: Show Properties based on Customer Preferences

    Posted 04-26-2017 23:38
    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.


  • 18.  RE: Show Properties based on Customer Preferences

    Posted 04-26-2017 23:53
    It was the Report.  I had "=" Ask_User

    doh!

    I've learned something new...  brilliant Mark!


  • 19.  RE: Show Properties based on Customer Preferences

    Posted 04-26-2017 23:55
    So success??  It's all working?  Hurray!


  • 20.  RE: Show Properties based on Customer Preferences

    Posted 04-27-2017 21:23
    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])


  • 21.  RE: Show Properties based on Customer Preferences

    Posted 04-27-2017 21:47
    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.


  • 22.  RE: Show Properties based on Customer Preferences

    Posted 04-27-2017 21:49
    The field isn't forced so it could be empty.
    I guess that makes things more complicated ?


  • 23.  RE: Show Properties based on Customer Preferences

    Posted 04-27-2017 22:12
    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?


  • 24.  RE: Show Properties based on Customer Preferences

    Posted 04-27-2017 22:34
    Yes, properties are only 1 thing :)


  • 25.  RE: Show Properties based on Customer Preferences

    Posted 04-28-2017 14:18
    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. 


  • 26.  RE: Show Properties based on Customer Preferences

    Posted 04-28-2017 15:33
    OK, that looks really interesting.
    I'll do that later today and let you know how it goes.
    thanks Mark.


  • 27.  RE: Show Properties based on Customer Preferences

    Posted 04-28-2017 15:53
    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.


  • 28.  RE: Show Properties based on Customer Preferences

    Posted 04-28-2017 16:00
    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


  • 29.  RE: Show Properties based on Customer Preferences

    Posted 04-28-2017 17:45
    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, ";")


  • 30.  RE: Show Properties based on Customer Preferences

    Posted 04-28-2017 17:56


  • 31.  RE: Show Properties based on Customer Preferences

    Posted 04-28-2017 18:17
    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, ";"))


  • 32.  RE: Show Properties based on Customer Preferences

    Posted 04-28-2017 18:27
    Ho Ho!!!
    Thats a great trick to keep up the sleeve LOL!
    Marvellous Mark!


  • 33.  RE: Show Properties based on Customer Preferences

    Posted 04-28-2017 18:28
    That is great that its working.  Thx for letting us know.


  • 34.  RE: Show Properties based on Customer Preferences

    Bronze
    Contributor
    Posted 04-26-2017 13:45
    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.


  • 35.  RE: Show Properties based on Customer Preferences

    Posted 04-26-2017 16:17
    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


  • 36.  RE: Show Properties based on Customer Preferences

    Posted 04-26-2017 16:35
    This looks like it would do the trick


  • 37.  RE: Show Properties based on Customer Preferences

    Bronze
    Contributor
    Posted 04-26-2017 17:53
    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