A5W Page Filter and Order Syntax

[carlbsmith]

Re: A5W Page Filter and Order Syntax

I basically followed everything Josh suggested above, with a slight difference on the html map page. His had an onclick event when a state was selected in the dropdown that would match an abbreviation to the state ID. I simply created a URL for each of the 50 states that had the state abbreviation already in it:

So you click on South Carolina, and this is the link:

https://snap.scansourcesecurity.com/sales.a5w?state=SC

I think his javascript was basically doing the same thing based on which drop down you select.


I have this working great now, and if I click on South Carolina, it takes me to all records where SC is set as the state. My follow up question to that is, what if I have SC, NC, and TX for that record? This method doesn't show records that have multiple entries. Is there a way to tweak this?

[Davidk]

Re: A5W Page Filter and Order Syntax

That looks good. It's a little bit of work and involves an extra callback... but works nicely.

Here's a quick video of a UX component using the Raphaeljs library. There's only one onclick event, no callback to set a US State value, and no extra page to call. The List still does a full data refresh once it gets the State value but this could be eliminated as well by just filtering the List client-side. And it even looks good on an iPhone.

https://youtu.be/rCojg00r_FI

BTW, the bird you hear in the background is a Mockingbird which was sitting just outside on a branch... what a great voice.

[carlbsmith]

Re: A5W Page Filter and Order Syntax

Interesting. Is there any tutorial anywhere that explains that in more detail? And would it support the multi state scenario I described above?

[coleresources]

Re: A5W Page Filter and Order Syntax

Carl,

David is leading you towards a much better long-term solution in my opinion. I've become far too dependent on callbacks when you really are supposed to push as much as you can to the client side and avoid callbacks wherever possible.

I think as David suggested (and there is probably a better way) you can create a comma separated value for the selected states and your filter would now become instead of state = :state, it would now look something like state IN :state where the value of the argument :state would look like CA,TX,HI

[Davidk]

Re: A5W Page Filter and Order Syntax

I guess for state, I could do comma separated values: NC,SC,TX, etc. But when I try to filter by SC for example, it doesn't return a field with multiple options, I guess because it is looking for just SC. Wondering if there is a better way.

Carl, it's a bit unclear what you'd like to do here. You select a State... SC... and get back a list of Sales Reps for SC. You then Select a Sales Rep and transition to that person's info. In that info, do you also want to see a list of other States that person is a Sales Rep for?

[carlbsmith]

Re: A5W Page Filter and Order Syntax

I guess what I am trying to say is that a sales rep might cover multiple states. So John Smith might cover North Carolina and South Caroline. I need his name to come up when I click on either of those states.

[Davidk]

Re: A5W Page Filter and Order Syntax

Unless you want to write CRUD routines to maintain a comma delimited column per Sales Rep... which is neither advisable... nor fun... then just follow a normal approach to your table...

RecId RepId State
1 1 SC
2 1 NC

[coleresources]

Re: A5W Page Filter and Order Syntax

Carl,

What type of back-end database are you working with? What I usually do in these instances is create a table in my database to map a sales rep to each state he/she needs to be associated with. I would then create a view that returns the appropriate records. Once that is in place everything will work as expected. If you can share what type of database you're working with I can help you see this to fruition.

[carlbsmith]

Re: A5W Page Filter and Order Syntax

Josh, just a standard alpha database on this project. David, not sure I follow your table example above. Thanks.

[CharlesParker]

Re: A5W Page Filter and Order Syntax

David that map is sweet, any chance you want to share that thing? I want the component to build a phone gap app to find some of my users...THAT would save me a bunch of time no doubt - nice job!

[carlbsmith]

Re: A5W Page Filter and Order Syntax

Thanks for all the feedback thus far. Just wondering if anyone has any ideas on how to implement the idea mentioned above that a sales rep could possibly be assigned to multiple states, not just a single state.

[carlbsmith]

Re: A5W Page Filter and Order Syntax

Any other feedback from anyone? I am so close to getting this to do what I need.

[coleresources]

Re: A5W Page Filter and Order Syntax

Carl,

The table data that you are basing your list control on needs to have a record of your sales agent for each state they are assigned to. I've never developed an application with a dbf back-end so I am unsure as to the best approach with dbf's.

In the SQL world, one way to accomplish this would be to create two tables and a view. I would have a sales_rep table, a sales_rep_state_mapping table, and a sales_rep_state_view

So my sales_rep table would look like...

rep_ID
rep_name
rep_email
rep_phone

I'd create one record for John Smith who is a sales rep assigned to Tennessee and Alabama

1|John Smith|[email protected]|(555)555-5555


I'd have a second table sales_rep_state_mapping
recordID
rep_id
state

I'd create two rows in sales_rep_state_mapping.

1|1|AL
2|1|TN

Now I'd create a join marrying sales_rep and sales_rep_state_mapping, the link being rep_id. I'd make sure to return the rows from the sales_rep_state_mapping table. Once you've created the view, I'd base the list on the data that the view returns.

I hope this makes sense. You could create multiple entries in a dbf table to accomplish the same thing but it would be pretty sloppy from a database design perspective. I don't think you can use dbf sets on the web side of Alpha which would be the equivalent of a view in the SQL world. If you can I'd make the change to a mysql or MS SQL express back-end. I'm sure there are many other options as well but DBF seems very limiting to me compared to your other free SQL options.

[carlbsmith]

Re: A5W Page Filter and Order Syntax

Josh,

I took your advice and have started migrating my database to SQL. It does seem to be more powerful, and creating views like you suggested is helpful.

I've created a primary table that has the base sales info (name, phone, email, picture, etc), and joined that to my secondary table where I show the different state/regions that each person is in. I do like this scenario from the aspect that I don't have to load things like a sales reps picture each time. It does seem to be a more efficient way to do it.

However, just wondering if there is still even a better way beyond this. Because, for example, if I have a sales rep that covers the east cost, there still has to be 20-30 entries for that one sales rep, one for each state in the secondary table. I think what I would love is if there was a way to set it up like checkboxes, so when I had a new rep, I can just hit a bunch of checks for the states they are in. But the key to that being that I need the check for each state to create a new row in my database. If my state/region field just has a bunch of states separated by commas, that doesn't work for my application unfortunately. I need to have separate rows for each state.

Anybody have any additional thoughts on this? Thanks!