find/ date-range in portals

Leb i Sol · March 17, 2003

please take a look at the image attached...it will (hopefully) be more direct than me trying to explain the problem

of course, this is having CDML in mind

in other words what is the input tag for the filed in Portal:

---SEARCH.html---------

...

..

.

<input name="RELATElocation::Date" type="text" id="RELATElocation::Date" .......

..

.

----------------------------------------------

can this work at all?...(

Leb i Sol · March 18, 2003

or is this one of those "limitations" of Portals?

...OR probably Developer (m _ y) Limitation

Leb i Sol · March 19, 2003

nobody knows the answer?

oh where did those gurus go...heeeeelp!

Garry Claridge · March 20, 2003

I am guessing that you are attempting to edit Portal rows. If so this is where you use a number to indicate which Portal row it is. For example,

<input name="RELATElocation::Date.1" type="text" id="RELATElocation::Date" .......

You would use ".0" to create a new Portal-Row.

You can auotmate the numbering of the rows with:

<input name="RELATElocation::Date.[FMP-CurrentPortalRowNumber]" type="text" id="RELATElocation::Date" .......

Hope this helps.

Garry

Garry Claridge · March 20, 2003

Whoops, I've now re-read the question. You are attempting to search on a Portal entry.

This should be OK. However, you may wish to test on something other than a Date field. Be careful using dates, make sure that they are formatted properly when submitted.

Good Luck.

Garry

Garry Claridge · March 20, 2003

I've just had another look at your image. You may find that working directly on the related file will improve your chance of success.

All the best.

Garry

Leb i Sol · March 20, 2003

Hi Garry!

I tryed but with CDML in mind I can not sort properly....the totals get changed since the file with the portal on it contains 5 records (each record - different Location...."date range" and "Location" are parameters from the Search)...

does this make sense?

date range per each Location(record)which has its own Totals!?

What do you suggest?

thank you sir

Garry Claridge · March 20, 2003

You may want to look at using the find on the parent file and an InlineAction on the related file. You can use a little bit of Javascript to do the totals etc.

You can pass the date range, and Key data, to the InlineAction with Tokens.

Show us a bit more of your form and we will split it for you.

All the best.

Garry

Leb i Sol · March 20, 2003

ok great!

I have been struggling with this for some time and even considering re-designing my DB...which is .... so any help WOULD BE VERY nice

the core of search

--------SEARCH.html-----------------------

Select Location and Date Range:

<option value="" selected>- All Locations</option>

<option value="ONTARIO P">ONTARIO P</option>

<option value="ONTARIO B">ONTARIO B</option>

</select>

</form>

------------------------------------------------------

my goal on Results page would be:

---AllResults.html----------------------------

for All Location Seletced on a date range

.......from: 1/1/2002 to : 1/1/2003............

LocationName| subtotal | total

................................................

ONTARIO P......[its_subtotal]...[its_total]

ONTARIO B......[its_subtotal]...[its_total]

NY......[its_subtotal]...[its_total]

PDC......[its_subtotal]...[its_total]

LA......[its_subtotal]...[its_total]

-------------------------------------------------------

where the sub and totals are calcualted fields and pulled from LineItem.fp5 {(x * y) in LineItem.pf5 and totaled into =z in Location.fp5}and are NOT in the portal(s)

Each Location is a record in Location.fp5...so all records ALWAYS would be 5 with its own portals ....as many as locations

I hope I make myself clear enough...

I am not that great ( rollover and small things are ok) in JavaScript so any help I would really apprechiate!

thanx Gary and everybody!

Leb i Sol · March 20, 2003

Hi Garry!

Another side note:

I read about you saying that portals are rather slow and the way I have them setup they WILL GROW daily....should I dump the portal idea? I wouldn't mind having just a "lonely Parent file wihout a relation and children" ( I guess a "single" file ) if I KNEW how to use the JavaScript to sort the whole thing....I know a bit about tokens and in-lines.

The only true reson I have portals is because I didn't know how to sort single file with multiple parameters.....so my

original thought was....maybe easier than this portal deal:

---SingleFile.fp5---------------

LocationName:[___]<---5 possible location entires

...more fields here

..

.

Date:[____]

.............calcualation fields

Total:{___}

Subtotal:{__}

---------------------------------------------

this file is easily sorted:

FROM[1/1/2002] to [1/1/2003]

Location=[ LA ]

and Totals return just fine.

.BUT the problem with this file is that I can not return ALL Locations with THIER totals...since the toals returned are always for the records found...and each location can have more entry(records) than the other.If sorted by "All Locations" I get Totals for ALL the records together rather than individual(locational) totals

so this is why I abondoned SingleFile.fp5 and turned into related files and Portals....

and got Stuck Again

heeeeelp....I am in this loop and getting ~~~~

ok..enough I will let you guys read about all my "problems"

thank you thank you thank YOU

Anatoli · March 20, 2003

RE: I read about you saying that portals are rather slow

I've burned my fingers with 50+ records in portal on web.

Worked beautifully, but much slower than direct file access.

Leb i Sol · March 20, 2003

ok....now we are split.....

is it worth it or not going through all this process if my portals will get "packed" with records and filtering/softing will take some Mega Response Time over www?

Summary..pull... will get most of the data from outside the portal (totals)but the Date range will be from portal itslef.

Would it be better to just "flatten" the file!? As mentioned in my post prior to this sorting would work and I guess I could use some IF- and 5 Inline statements ( for 5 locations)to generate this summary...but the ultimate Q...WHAT IS BETTER

thank you guys for your time

Garry Claridge · March 21, 2003

You can look at an inverse relationship. That is, relate the data file back to the Location file.

I will have some time on the weekend to give an example.

All the best.

Garry

Leb i Sol · March 21, 2003

thank you Garry!

I was also considering a workarround- remove relationships - use inline + FM-if for display(which is what really counts).....but the more ideas are out there the better!

Thanx again!

Garry Claridge · March 21, 2003

OK, here are the assumptions.

Two files exist: locations.fp5 and lines.fp5

locations.fp5 has fields {location_id, locationname}

lines.fp5 hasfields {line_id, date, customer_name, po#, qty}

lines.fp5 has two summary field:

totalqty - "Total of qty"

sales - "Count of line_id"

lines.fp5 has a Relationship to locations.fp5 based on location_id

lines.fp5 has a Layout called "web". It contains all fields plus "locations::locationname"

Here is the "search.html" file:

<html>

<head>

	<title>Leb Test</title>

	<meta name="generator" content="BBEdit 7.0.3">

</head>

<body>

<form action="FMPro" method="post">

<input type="hidden" name="-db" value="seb_lines.fp5">

<input type="hidden" name="-lay" value="web">

<input type="hidden" name="-format" value="leb_result.html">

Enter Location ID:<input type="text" name="location_id" value=""><br>

<input type="hidden" name="-op" value="gte">

Enter Start Date:<input type="text" name="date" value=""><br>

<input type="hidden" name="-op" value="lte">

Enter End Date:<input type="text" name="date" value=""><br>

<input type="submit" name="-find" value="Find">

</form>

</body>

</html>





Here is the "results.html" file:

<html>

<head>

	<title>Leb Results</title>

	<meta name="generator" content="BBEdit 7.0.3">

</head>

<body>

[FMP-Field: seb_locations::location_name] | [FMP-Field: totalqty] | [FMP-Field: sales]

</body>

</html>

Hope these help.

Garry

Leb i Sol · March 21, 2003

ok.....this looks like some "food for the brain"....

I am very grateful for taking your time and posting these great solutions. I am all over the place and learning A LOT

I hope some other people develop their ideas through these posts!

Thanx Garry

Garry Claridge · March 21, 2003

The next step is for listing all Locations with relative summaries and a grand-total.

Maybe later in the weekend. I'm heading up-town for breakfast now (it is Saturday morning here ).

All the best.

Garry

Leb i Sol · March 24, 2003

hi Garry!

we been cross-posting simmilar issue....you know the [FMP-If and a few In-line actions......well both posts are triggered by the same problem...as you can tell by now.

So what to do...develop this version of related files or try to struggle with 5 Inline-Actions :?

p.s. I am month 1/2 late and I still don't have this D$%@#%#$ working...I get into one thing...develop it and then little voice from FM comes on: "...Ha ha - now you can't use your DB with CDML have a nice day"

Leb i Sol · March 25, 2003

ok ok...before I truble you more....( and before I find myself cought in one of those impossible IF loops) I can not have users type Location ID and a search criteria( they can not ever remember the password let alone which digit = which location name)...is this still duable for "All Locations"

with

their Own Sub and Totals??

Garry Claridge · March 25, 2003

Most things are doable Some just take longer than others.

I haven't finished the multi-location version yet. I will have another chance to work on it tonight. I believe it will be the best way to go as it should be the most simple.

All the best.

Garry

Leb i Sol · March 25, 2003

Hey Garry!

Quick question(s):

-Why are we dragging the LocationName back from Locations.fp5 fore the second time?

It is already a lookup from LOCATIONS.fp5 ......?!

Shouldn't I be sorting LOCATIONS.fp5 file since it has totals associated with each location or is THIS the whole point/my mistake/ of "inverse releationship" and avoiding the portal sorting ! hm...now that I said it....( these totals are in LOCATION.fp5 there just for the visual "ease" I can also dragg them back into in LINEITEM.fp5 on the "web" layout...that is what you seuggested

--------------------------------------------------------GARRY--------------

lines.fp5 has two summary field:

totalqty - "Total of qty" <--TOTALqty (Sum(Qty))

sales - "Count of line_id" <--- TOTALamount (Sum(Amount))

----------------------THIS IS HAPPENING IN LOCATION.fp5 not in LINEITEM.fp5----?

if not why would I need the Location.fp5 file at all?

PLEASE take a look at the image attached so we are on the same page!

thanx

sorry for editing post so many times...but the more I write the more questions I have (the better understanding hopefully ):

Leb i Sol · March 25, 2003

"if not why would I need the Location.fp5 file at all? "

what I meant is that I can do these Total calcualations in LINEITEM.fp5 file and the LocationName/ID can be easily inserted in LINEITEM wihout lookup.....For results I am now sorting the lineitem.fp5 file???

I mean....at the bottom of it all I am back to only needing/sorting 1 file (lineitem.fp5)...therefor...WHY NOT just keep the LINEITEM.fp5 file and forget all the relationships/lookups?!

this is not an "invoice-ing" system it is only a "total-ing" system!

so....if am I am doing all the searching/sorting on the single file in order to avoid portals....well... I am back to @#$%@ post about [FMP-IF...InlineActions that lead to nothing and eventially to creation of this post..... aghhhhhhhh my head hurts! !!! !! !!! !! !!!

so what is the next step on multi-location?

hurry Oby-One you'r my only hope!:

Garry Claridge · March 25, 2003

The "Locations.fp5" file is a related file with a relationship based on "location_id". Hence, you do not need to store both the "location_id" and the "locationname" in the "lineitem.fp5" file.

However, if the Locations will not vary, you may-as-well have them as a Valuelist and store them as data. You will not need the "location_id" field in this case.

The "reports.fp5" is new to me. I guess that the "lineitem.fp5" file contains the line-items for each Report.

I assume the Aim of the web-page is to show sales by Location for Date periods. You can use summary fields for some of these reports.

I am working on that page right-now

All the best.

Garry

Leb i Sol · March 25, 2003

thank you thank thank you....

you understand

You don't know how many topic-posts this thing has triggered just to explain my idea. "UGO" was the one that advised me to do this relationship style and....well...I learned A LOT by working on them but still got nothing resolved.

Yes the page is supposed to show Sales per Location based on time period.And after - "go to details" page where I would see Location and totals for each day.....and then each sale/PO on that date.

SO the ULTIMATE RESULTS PAGES:

<============= 1st ===================>

Location | Total Qty | Total Amount|

-------------------------------------

Location1| x| $X

Location2|X|$X

...

..

<=======================================>

<======================2nd==============>

............LOCATION 1.................

Date| Total qty | Total Amount|

--------------------------------

1/1/2002| X| $X

1/2/2002|

1/3/2002|

....

<==========================================>

<====================== 3rd ==============>

..................LOCATION 1 on 1/1/2002....

PO_No|Customer| Qty|Amount

-------------------------------

1001|JACK|x....|$X....

1003|Smith|X....|$X...

<========================================>

so in some sense ability to generate DAILY,WEEKLY...any time period reports and IF wanted details behind it!

So you nailed it Garry...it's a sales report not Invoice Report ( as many here on forum were dealing with)

you saved this little hair I have left on my head ....I could almost post "Before" and "After" images of my self...

rested-willing-excited.......tired-confused-sad-no hair....LOL

thanx again!

Garry Claridge · March 25, 2003

Here is the basic Format file which summarises the Locations:

<html>

<head>

	<title>Leb Results All</title>

	<meta name="generator" content="BBEdit 7.0.3">

</head>

<body><script>var tot_qty = 0; var loc_total = 0; var curr_loc = "Location";

[FMP-Record]

if (curr_loc == "[FMP-Field:locations::location_name]")

	{

	loc_total += [FMP-Field:qty];

    tot_qty += [FMP-Field:qty]; }

else

    {

    document.write(curr_loc + " | " + loc_total + "<br>");

    curr_loc = "[FMP-Field:locations::location_name]";

    loc_total = [FMP-Field:qty];

    tot_qty += [FMP-Field:qty];

    } ;     	

[/FMP-Record]

document.write(curr_loc + " | " + loc_total + "<br>");

document.write("Total Quantity: " + tot_qty);</script>

</body>

</html>

This is the Form which called it:

<body>

<form action="FMPro" method="post">

<input type="hidden" name="-db" value="leb_lines.fp5">

<input type="hidden" name="-lay" value="web">

<input type="hidden" name="-format" value="leb_result_all.html">

Enter Location:<select name="locations::location_name" value="">

<option value="">All</option>

[FMP-ValueList: location_name, List=locations]

<option value="[FMP-ValueListItem]">[FMP-ValueListItem]</option>

[/FMP-ValueList]</select><br>

<input type="hidden" name="-op" value="gte">

Enter Start Date:<input type="text" name="date" value=""><br>

<input type="hidden" name="-op" value="lte">

Enter End Date:<input type="text" name="date" value=""><br>

<input type="hidden" name="-sortfield" value="locations::location_name">

<input type="submit" name="-find" value="Find">

</form>

</body>

Hope this helps.

Garry

Leb i Sol · March 25, 2003

ooooo man

Great..thanx

lets start (if you don'r mind) from FORM:

I am searching the LINEITEM.fp5 that has a field "locations" that looks-up/pulls data from field "location_name" in LOCATION.fp5? right!?

"Enter Location:<select name="locations::location_name" value="">"

this is suppsed to be a dropdown list that gets populated with all location names and ALL option right?!

LOCATION.fp5 and its field location name right? All see is "ALL"....

Leb i Sol · March 25, 2003

the problem was resolved:

"this is suppsed to be a dropdown list that gets populated with all location names and ALL option right?! "

Meltdown wrote...

"1) Most important step, easiest to overlook/misunderstand. Check to make sure the valuelist is defined in the database you are calling up when the HTML page is displayed. And remember, the valuelist must be in the database used to display the HTML page, not the database the HTML page will add/edit."

sorry....I am new to all this so 1 step at the time!

Garry Claridge · March 25, 2003

Because you have "locationname" as a data field in the "lineitems.fp5" file, you can use the Locations Valuelist from the "lineitems.fp5" file instead of "locations::location_name".

Any "locations::location_name" that you see you can replace with "locationname".

You can totally disregard the "locations.fp5" file.

Good Luck.

Garry

Leb i Sol · March 25, 2003

2 last words for you Garry:

THANK YOU

{---{

Garry Claridge · March 27, 2003

Here is a method of displaying the details for a location by using tokens to pass the dates.

This is the revised "search" page:

<head>

	<title>Leb Test</title>

	<script>

	   function adddates()

	      {

	      document.searchform.elements["-token.1"].value = document.searchform.elements[5].value;

	      document.searchform.elements["-token.2"].value = document.searchform.elements[7].value;

	      document.searchform.submit();

	      };

	</script>

</head>

<body>

<form name="searchform" action="FMPro" method="post" onsubmit="adddates(); return false;">

<input type="hidden" name="-db" value="lineitems.fp5">

<input type="hidden" name="-lay" value="web">

<input type="hidden" name="-format" value="results.html">

Enter Location:<select name="locationname" value="">

<option value="">All</option>

[FMP-ValueList: location_name, List=locations]

<option value="[FMP-ValueListItem]">[FMP-ValueListItem]</option>

[/FMP-ValueList]</select><br>

<input type="hidden" name="-op" value="gte">

Enter Start Date:<input type="text" name="date" value=""><br>

<input type="hidden" name="-op" value="lte">

Enter End Date:<input type="text" name="date" value=""><br>

<input type="hidden" name="-sortfield" value="locationname">

<input type="hidden" name="-token.1" value="">

<input type="hidden" name="-token.2" value="">

<input type="hidden" name="-find">

<input type="submit" name="-find" value="Find">

</form>

</body>

Here is the revised "results" page:

<head>

	<title>Leb Results All</title>

	<script>

	   function showdetails(location)

	      {

	      document.detailsform.locationname.value = location;

	      document.detailsform.submit();

	      } ;

	 </script>

</head>

<body>

<form name="detailsform" action="FMPro" method="post" target="detailsframe">

<input type="hidden" name="-db" value="lineitems.fp5">

<input type="hidden" name="-lay" value="web">

<input type="hidden" name="-format" value="loc_details.html">

<input type="hidden" name="locationname" value="">

<input type="hidden" name="-op" value="gte">

<input type="hidden" name="date" value="[FMP-CurrentToken:1]">

<input type="hidden" name="-op" value="lte">

<input type="hidden" name="date" value="[FMP-CurrentToken:2]">

<input type="hidden" name="-find">

</form>

<script>var tot_qty = 0; var loc_total = 0; var curr_loc = "Location";

[FMP-Record]

if (curr_loc == "[FMP-Field:locations::location_name]")

	{

	loc_total += [FMP-Field:qty];

    tot_qty += [FMP-Field:qty]; }

else

    {

    document.write("<a href="#" onclick="showdetails('" + curr_loc + "');">" + curr_loc + "</a> | " + loc_total + "<br>");

    curr_loc = "[FMP-Field:locations::location_name]";

    loc_total = [FMP-Field:qty];

    tot_qty += [FMP-Field:qty];

    } ;     	

[/FMP-Record]

document.write("<a href="#" onclick="showdetails('" + curr_loc + "');">" + curr_loc + "</a> | " + loc_total + "<br>");

document.write("Total Quantity: " + tot_qty);</script>

</body>

This will present you with the records for the selected location between the target dates.

All the best.

Garry

Leb i Sol · March 27, 2003

This is ...just....beyond and then a few feet more !

absolutely great!

eliminates portals.....reduces relationships to NONE and uses only LINEITEMS.fp5 to genearate Sales Reports based on any date and any location(point of sale)

GREAT job Garry !!!

who said you can't escape from prison with only a spoon in your pocket!

Sign In

find/ date-range in portals

Recommended Posts

Create an account or sign in to comment

Create an account

Sign in

Important Information