How? Sort lookup by different column
How? Sort lookup by different column
Hi,
I am using a custom SQL query in an advanced lookup.
Unfortunately AG always sorts by the column I have selected for view (see images)
Is there a way to force AG to not sort, but just use what it get's from my SQL?
Of course sorting is nice (in general) , but this is done in advance much more sophisticated by the SQL.
AG Setting and here you can see, that the sorting is done not numerical (as I did in my query, see below)
Wrong: Correct:
This is the correct sort I need (and which is returned by my SQL)
Lagerort_Kurz ASC
ID_posX ASC
ID_posY DESC
ID_posZ DESC
All this is reflected in the automatically created Location_Auto column - which should be shown in AG. ID_locationNum is PK and saved in the master table.
Olaf
I am using a custom SQL query in an advanced lookup.
Unfortunately AG always sorts by the column I have selected for view (see images)
Is there a way to force AG to not sort, but just use what it get's from my SQL?
Of course sorting is nice (in general) , but this is done in advance much more sophisticated by the SQL.
AG Setting and here you can see, that the sorting is done not numerical (as I did in my query, see below)
Wrong: Correct:
This is the correct sort I need (and which is returned by my SQL)
Lagerort_Kurz ASC
ID_posX ASC
ID_posY DESC
ID_posZ DESC
All this is reflected in the automatically created Location_Auto column - which should be shown in AG. ID_locationNum is PK and saved in the master table.
Olaf
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
Re: How? Sort lookup by different column
Hi,
can you post your custom sql that you use for the lookup.
can you post your custom sql that you use for the lookup.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
Re: How? Sort lookup by different column
Hi pbötcher,
sure - though not sure if it helps:
Olaf
sure - though not sure if it helps:
Code: Select all
SELECT *
FROM (SELECT ecomo_Location.ID_LocationNum,
ecomo_Location.Location_Auto,
ecomo_Lagerorte.Lagerort_Kurz,
ecomo_Location.ID_posX,
ecomo_Location.ID_posY,
ecomo_Location.ID_posZ
FROM (SELECT ecomo_Location.ID_Lagerorte,
ecomo_Location.ID_posX,
ecomo_Location.ID_posY,
Min(ecomo_Location.ID_posZ) AS Min_ID_posZ
FROM ecomo_Location
INNER JOIN (SELECT ecomo_Location.ID_LocationNum,
Count(ecomo_Container.ID_Location) AS Count_ID_Location
FROM ecomo_Location
LEFT JOIN ecomo_Container
ON ecomo_Container.ID_Location = ecomo_Location.ID_LocationNum
GROUP BY ecomo_Location.ID_LocationNum) Query1
ON ecomo_Location.ID_LocationNum = Query1.ID_LocationNum
WHERE ( Query1.Count_ID_Location = 0 )
OR ( ecomo_Location.ImmerZeigen = 'Ja' )
GROUP BY ecomo_Location.ID_Lagerorte,
ecomo_Location.ID_posX,
ecomo_Location.ID_posY,
Query1.Count_ID_Location,
ecomo_Location.ImmerZeigen) Query1
INNER JOIN ecomo_Location
ON ecomo_Location.ID_Lagerorte = Query1.ID_Lagerorte
AND ecomo_Location.ID_posX = Query1.ID_posX
AND ecomo_Location.ID_posY = Query1.ID_posY
AND ecomo_Location.ID_posZ = Query1.Min_ID_posZ
INNER JOIN ecomo_Lagerorte
ON ecomo_Location.ID_Lagerorte = ecomo_Lagerorte.ID_LagerorteNum
ORDER BY ecomo_Lagerorte.Lagerort_Kurz,
ecomo_Location.ID_posX,
ecomo_Location.ID_posY DESC,
ecomo_Location.ID_posZ DESC) ecomo_Location
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
Re: How? Sort lookup by different column
Hi,
just to be sure. This is the query that you have in the APPGini Advanced -> "User this SQL query .... " field.
just to be sure. This is the query that you have in the APPGini Advanced -> "User this SQL query .... " field.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
Re: How? Sort lookup by different column
Hi pbötcher,
yes, that is correct. Of course I created the query with another program. The query runs fine in phpmyadmin and shows the output above in picture 3.
The query also runs find in AG - but it seems AG forces an alphabetical sort on the first column of the query (not even the selected output column).
How I know: I put Location_Auto for a test in the last position. AG shows now the first column (Lagerort_Kurz) in the generated application, even though I selected ID_LocationNum in AG (picture 1)
Olaf
yes, that is correct. Of course I created the query with another program. The query runs fine in phpmyadmin and shows the output above in picture 3.
The query also runs find in AG - but it seems AG forces an alphabetical sort on the first column of the query (not even the selected output column).
How I know: I put Location_Auto for a test in the last position. AG shows now the first column (Lagerort_Kurz) in the generated application, even though I selected ID_LocationNum in AG (picture 1)
Olaf
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
Re: How? Sort lookup by different column
Hi,
can you try this:
can you try this:
Code: Select all
SELECT *
FROM (SELECT ecomo_Location.ID_LocationNum,
ecomo_Location.Location_Auto,
ecomo_Lagerorte.Lagerort_Kurz,
ecomo_Location.ID_posX,
ecomo_Location.ID_posY,
ecomo_Location.ID_posZ
FROM (SELECT ecomo_Location.ID_Lagerorte,
ecomo_Location.ID_posX,
ecomo_Location.ID_posY,
Min(ecomo_Location.ID_posZ) AS Min_ID_posZ
FROM ecomo_Location
INNER JOIN (SELECT ecomo_Location.ID_LocationNum,
Count(ecomo_Container.ID_Location) AS Count_ID_Location
FROM ecomo_Location
LEFT JOIN ecomo_Container
ON ecomo_Container.ID_Location = ecomo_Location.ID_LocationNum
GROUP BY ecomo_Location.ID_LocationNum) Query1
ON ecomo_Location.ID_LocationNum = Query1.ID_LocationNum
WHERE ( Query1.Count_ID_Location = 0 )
OR ( ecomo_Location.ImmerZeigen = 'Ja' )
GROUP BY ecomo_Location.ID_Lagerorte,
ecomo_Location.ID_posX,
ecomo_Location.ID_posY,
Query1.Count_ID_Location,
ecomo_Location.ImmerZeigen) Query1
INNER JOIN ecomo_Location
ON ecomo_Location.ID_Lagerorte = Query1.ID_Lagerorte
AND ecomo_Location.ID_posX = Query1.ID_posX
AND ecomo_Location.ID_posY = Query1.ID_posY
AND ecomo_Location.ID_posZ = Query1.Min_ID_posZ
INNER JOIN ecomo_Lagerorte
ON ecomo_Location.ID_Lagerorte = ecomo_Lagerorte.ID_LagerorteNum
) ecomo_Location
ORDER BY ecomo_Lagerorte.Lagerort_Kurz,
ecomo_Location.ID_posX,
ecomo_Location.ID_posY DESC,
ecomo_Location.ID_posZ DESC
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
Re: How? Sort lookup by different column
Hi pbötcher,
no, your code did not work (and not in phpmyadmin).
I think I corrected your code (below) - but this also throws a never ending search (image below)
problem was in the last ORDER you took the wrong table name
I then tried your embeded query suggestion from another thread that I started with this SQL (below) ... did not work either - it shows the problematic (wrong) sort order (image below).
It seems, that AG breaks the SQL when it ends (at least in my case) with an ORDER.
Olaf
no, your code did not work (and not in phpmyadmin).
I think I corrected your code (below) - but this also throws a never ending search (image below)
problem was in the last ORDER you took the wrong table name
ORDER BY ecomo_Lagerorte.Lagerort_Kurz,
ecomo_Location.ID_posX,
ecomo_Location.ID_posY Desc,
ecomo_Location.ID_posZ Desc
Code: Select all
Select
ID_LocationNum,
Location_Auto,
Lagerort_Kurz,
ID_posX,
ID_posY,
ID_posZ
From
(Select
ecomo_Location.ID_LocationNum,
ecomo_Location.Location_Auto,
ecomo_Lagerorte.Lagerort_Kurz,
ecomo_Location.ID_posX,
ecomo_Location.ID_posY,
ecomo_Location.ID_posZ
From
(Select
ecomo_Location.ID_Lagerorte,
ecomo_Location.ID_posX,
ecomo_Location.ID_posY,
Min(ecomo_Location.ID_posZ) As Min_ID_posZ
From
ecomo_Location Inner Join
(Select
ecomo_Location.ID_LocationNum,
Count(ecomo_Container.ID_Location) As Count_ID_Location
From
ecomo_Location Left Join
ecomo_Container On ecomo_Container.ID_Location = ecomo_Location.ID_LocationNum
Group By
ecomo_Location.ID_LocationNum) Query1 On ecomo_Location.ID_LocationNum = Query1.ID_LocationNum
Where
(Query1.Count_ID_Location = 0) Or
(ecomo_Location.ImmerZeigen = 'Ja')
Group By
ecomo_Location.ID_Lagerorte,
ecomo_Location.ID_posX,
ecomo_Location.ID_posY,
Query1.Count_ID_Location,
ecomo_Location.ImmerZeigen) Query1 Inner Join
ecomo_Location On ecomo_Location.ID_Lagerorte = Query1.ID_Lagerorte
And ecomo_Location.ID_posX = Query1.ID_posX
And ecomo_Location.ID_posY = Query1.ID_posY
And ecomo_Location.ID_posZ = Query1.Min_ID_posZ Inner Join
ecomo_Lagerorte On ecomo_Location.ID_Lagerorte = ecomo_Lagerorte.ID_LagerorteNum) ecomo_Location
Order By
ecomo_Location.Lagerort_Kurz,
ecomo_Location.ID_posX,
ecomo_Location.ID_posY Desc,
ecomo_Location.ID_posZ Desc
Code: Select all
select * from
(Select
a.ID_LocationNum,
a.Location_Auto,
a.Lagerort_Kurz,
a.ID_posX,
a.ID_posY,
a.ID_posZ
From
(Select
ecomo_Location.ID_LocationNum,
ecomo_Location.Location_Auto,
ecomo_Lagerorte.Lagerort_Kurz,
ecomo_Location.ID_posX,
ecomo_Location.ID_posY,
ecomo_Location.ID_posZ
From
(Select
ecomo_Location.ID_Lagerorte,
ecomo_Location.ID_posX,
ecomo_Location.ID_posY,
Min(ecomo_Location.ID_posZ) As Min_ID_posZ
From
ecomo_Location Inner Join
(Select
ecomo_Location.ID_LocationNum,
Count(ecomo_Container.ID_Location) As Count_ID_Location
From
ecomo_Location Left Join
ecomo_Container On ecomo_Container.ID_Location = ecomo_Location.ID_LocationNum
Group By
ecomo_Location.ID_LocationNum) Query1 On ecomo_Location.ID_LocationNum = Query1.ID_LocationNum
Where
(Query1.Count_ID_Location = 0) Or
(ecomo_Location.ImmerZeigen = 'Ja')
Group By
ecomo_Location.ID_Lagerorte,
ecomo_Location.ID_posX,
ecomo_Location.ID_posY,
Query1.Count_ID_Location,
ecomo_Location.ImmerZeigen) Query1 Inner Join
ecomo_Location On ecomo_Location.ID_Lagerorte = Query1.ID_Lagerorte
And ecomo_Location.ID_posX = Query1.ID_posX
And ecomo_Location.ID_posY = Query1.ID_posY
And ecomo_Location.ID_posZ = Query1.Min_ID_posZ Inner Join
ecomo_Lagerorte On ecomo_Location.ID_Lagerorte = ecomo_Lagerorte.ID_LagerorteNum) a
Order By
a.Lagerort_Kurz,
a.ID_posX,
a.ID_posY Desc,
a.ID_posZ Desc) ecomo_Location
Olaf
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
Re: How? Sort lookup by different column
Hi,
I checked, at least in ajax_combo.php the query shows up the way I entered - without any additional sorting ...
Olaf
I checked, at least in ajax_combo.php the query shows up the way I entered - without any additional sorting ...
Olaf
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
Re: How? Sort lookup by different column
Hi,
I am investigating the problem. It seems to me ... I would like some feedback on this ... that the file ajax_combo.php is containing and start of the problem.
If you look into that file, you will find
When I use my (for testing adjusted with an extra order by) custom query
and test the regex (which seems to check if there is a custom order set) I get no results - it does not see, that my query is ordered already.
I tried on https://regex101.com and on https://regexr.com
When I change the regex (on the mentioned pages to) with modifiers global, multiline and case-insensitive /gim the order clause is found. Note: All order clauses are found (if you implement more than one) - which actually I do not see as a problem.
If a programmer can create an SQL for an advanced lookup field, he will be able to sort that field by SQL to his liking and is not in need for the Gini at this point.
If I change the regex to also the characters after the order by in the same line (!) are matched.
I am not sure right now what is done there in the code, if the order by is added when none is found or if the order contents (what is ordered by) is added to the string.
Please note, that this regex breaks the search box for me when I change it in ajax_combo.php.
Sidenote:
(the code above is followed by
which might be the same problem for WHERE)
Please some insight from others on this. Thank you.
Olaf
I am investigating the problem. It seems to me ... I would like some feedback on this ... that the file ajax_combo.php is containing and start of the problem.
If you look into that file, you will find
Code: Select all
if(preg_match('/ order by (.*)$/i', $combo->Query, $qm)){
$custom_order_by = $qm[1];
$combo->Query = preg_replace('/ order by .*$/i', '', $combo->Query);
}
Code: Select all
select * from (
Select
ecomo_Location.ID_LocationNum,
ecomo_Location.Location_Auto,
ecomo_Lagerorte.Lagerort_Kurz,
ecomo_Location.ID_posX,
ecomo_Location.ID_posY,
ecomo_Location.ID_posZ
From
(Select
ecomo_Location.ID_Lagerorte,
ecomo_Location.ID_posX,
ecomo_Location.ID_posY,
Min(ecomo_Location.ID_posZ) As Min_ID_posZ
From
ecomo_Location Inner Join
(Select
ecomo_Location.ID_LocationNum,
Count(ecomo_Container.ID_Location) As Count_ID_Location
From
ecomo_Location Left Join
ecomo_Container On ecomo_Container.ID_Location = ecomo_Location.ID_LocationNum
Group By
ecomo_Location.ID_LocationNum) Query1 On ecomo_Location.ID_LocationNum = Query1.ID_LocationNum
Where
(Query1.Count_ID_Location = 0) Or
(ecomo_Location.ImmerZeigen = 'Ja')
order by Query1.Count_ID_Location
Group By
ecomo_Location.ID_Lagerorte,
ecomo_Location.ID_posX,
ecomo_Location.ID_posY,
Query1.Count_ID_Location,
ecomo_Location.ImmerZeigen) Query1 Inner Join
ecomo_Location On ecomo_Location.ID_Lagerorte = Query1.ID_Lagerorte
And ecomo_Location.ID_posX = Query1.ID_posX
And ecomo_Location.ID_posY = Query1.ID_posY
And ecomo_Location.ID_posZ = Query1.Min_ID_posZ Inner Join
ecomo_Lagerorte On ecomo_Location.ID_Lagerorte = ecomo_Lagerorte.ID_LagerorteNum
Order By
ecomo_Lagerorte.Lagerort_Kurz,
ecomo_Location.ID_posX,
ecomo_Location.ID_posY Desc,
ecomo_Location.ID_posZ Desc
) ecomo_Location
I tried
Code: Select all
order by (.*)$
When I change the regex (on the mentioned pages to)
Code: Select all
(?:order\ by)
If a programmer can create an SQL for an advanced lookup field, he will be able to sort that field by SQL to his liking and is not in need for the Gini at this point.
If I change the regex to
Code: Select all
(?:order\ by)(.*)$
I am not sure right now what is done there in the code, if the order by is added when none is found or if the order contents (what is ordered by) is added to the string.
Please note, that this regex breaks the search box for me when I change it in ajax_combo.php.
Sidenote:
(the code above is followed by
Code: Select all
if(preg_match('/ where (.*)$/i', $combo->Query, $qm)){
$custom_where = $qm[1];
$combo->Query = preg_replace('/ where .*$/i', '', $combo->Query);
}
Please some insight from others on this. Thank you.
Olaf
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
Re: How? Sort lookup by different column
Hi,
thanks for correcting the code.
I cannot follow your point here. I tested on my testsystem and AppGini would present the results as I expected.
Can you post the result of
in the direct phpmyadmin sql
and then in what AppGini displays.
thanks for correcting the code.
I cannot follow your point here. I tested on my testsystem and AppGini would present the results as I expected.
Can you post the result of
Code: Select all
Select
ID_LocationNum,
Location_Auto,
Lagerort_Kurz,
ID_posX,
ID_posY,
ID_posZ
From
(Select
ecomo_Location.ID_LocationNum,
ecomo_Location.Location_Auto,
ecomo_Lagerorte.Lagerort_Kurz,
ecomo_Location.ID_posX,
ecomo_Location.ID_posY,
ecomo_Location.ID_posZ
From
(Select
ecomo_Location.ID_Lagerorte,
ecomo_Location.ID_posX,
ecomo_Location.ID_posY,
Min(ecomo_Location.ID_posZ) As Min_ID_posZ
From
ecomo_Location Inner Join
(Select
ecomo_Location.ID_LocationNum,
Count(ecomo_Container.ID_Location) As Count_ID_Location
From
ecomo_Location Left Join
ecomo_Container On ecomo_Container.ID_Location = ecomo_Location.ID_LocationNum
Group By
ecomo_Location.ID_LocationNum) Query1 On ecomo_Location.ID_LocationNum = Query1.ID_LocationNum
Where
(Query1.Count_ID_Location = 0) Or
(ecomo_Location.ImmerZeigen = 'Ja')
Group By
ecomo_Location.ID_Lagerorte,
ecomo_Location.ID_posX,
ecomo_Location.ID_posY,
Query1.Count_ID_Location,
ecomo_Location.ImmerZeigen) Query1 Inner Join
ecomo_Location On ecomo_Location.ID_Lagerorte = Query1.ID_Lagerorte
And ecomo_Location.ID_posX = Query1.ID_posX
And ecomo_Location.ID_posY = Query1.ID_posY
And ecomo_Location.ID_posZ = Query1.Min_ID_posZ Inner Join
ecomo_Lagerorte On ecomo_Location.ID_Lagerorte = ecomo_Lagerorte.ID_LagerorteNum) ecomo_Location
Order By
ecomo_Location.Lagerort_Kurz,
ecomo_Location.ID_posX,
ecomo_Location.ID_posY Desc,
ecomo_Location.ID_posZ Desc
and then in what AppGini displays.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
Re: How? Sort lookup by different column
Hi pbötcher,
direct SQL output of your query: AppGini output of your query (new record): Existing record: My query (wrong sort): Olaf
direct SQL output of your query: AppGini output of your query (new record): Existing record: My query (wrong sort): Olaf
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
Re: How? Sort lookup by different column
Hi again
the point I was trying to make is, that I question the correctness of the regex that is used in the ajax_combo.php.
What if the order colums are in the next line (after the return) - this way the regex does not seem to find anything.
Olaf
the point I was trying to make is, that I question the correctness of the regex that is used in the ajax_combo.php.
What if the order colums are in the next line (after the return) - this way the regex does not seem to find anything.
Olaf
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
Re: How? Sort lookup by different column
Hi again,
the setting
The problem above is embedded in this setting:
I have over sea ISO containers that are placed in a certain location. This location is divided in coordinates (row -> ID_posX, depth -> ID_posY, level -> ID_posZ). As there might be more than one area this is also attached as Lagerort. All this data is concated to "place": Lagerort*Reihe-Tiefe/Ebene so for example 8*1-2/3. This would be a place in area 8, row 1, depth (from the forlift drivers point of view) 2 and level 3. Below that are already two other containers (of course, as no container can hover in midair).
Now the forklift driver takes a container and takes it to another location. At this point my application is used to change that location. The driver therefore selects a new location from the list.
This list should of course be ordered and show (old solution): only places that are a) not empty or should always be show (ImmerZeigen=Ja) and b) not in midair, but the lowest available.
The new version (see below) is: only places that are a) have less containers than allowed (BelegtBei) in this place and b) not in midair, but the lowest available.
old version solution
pbötcher wrote me a PM after the problem was not solved here and suggested some code, that works (thank you very much here again):
First of all all custom SQL has to be a one line string without line breaks! This is very important, otherwise the regex will not find your ORDER.
This code sorts correctly and loads the rest of the list, when I scroll down, works with the ajax search and shows only the empty locations.
This is his code (I am using an image here, so you see, there is not much difference to the new code below, I also use SQL to make helping easier by copy&paste) again as sql:
the new (not working) solution
Now I changed my database to make locations not show if they are empty of ImmerZeigen=ja as above, but the user would be able to set a certain number that defines, when a location is full BelegtBei. A location will be shown in the dropdown list, if is has less entries in the table than the BelegtBei value is. This results in this SQL which works fine in phpmyadmin, returning the correct records in the correct order: again as sql:
the problem with the new SQL in the generated AppGini code
As you see, I marked two sections with a border in the images. This is the only difference and the result of the complete SQL is exactly the same.
But with the new SQL the generated AppGini files do not work anymore!
The Order is still correct and loading more from the list works too, but AJAX search does not work - the spinner keeps spinning but does not suggest anything as can bee seen in the picture below. As the SQL does work in Mysql I am requesting your help. I can not see any reason, why this happens but I think something is strange with the AJAX search.
Olaf
the setting
The problem above is embedded in this setting:
I have over sea ISO containers that are placed in a certain location. This location is divided in coordinates (row -> ID_posX, depth -> ID_posY, level -> ID_posZ). As there might be more than one area this is also attached as Lagerort. All this data is concated to "place": Lagerort*Reihe-Tiefe/Ebene so for example 8*1-2/3. This would be a place in area 8, row 1, depth (from the forlift drivers point of view) 2 and level 3. Below that are already two other containers (of course, as no container can hover in midair).
Now the forklift driver takes a container and takes it to another location. At this point my application is used to change that location. The driver therefore selects a new location from the list.
This list should of course be ordered and show (old solution): only places that are a) not empty or should always be show (ImmerZeigen=Ja) and b) not in midair, but the lowest available.
The new version (see below) is: only places that are a) have less containers than allowed (BelegtBei) in this place and b) not in midair, but the lowest available.
old version solution
pbötcher wrote me a PM after the problem was not solved here and suggested some code, that works (thank you very much here again):
First of all all custom SQL has to be a one line string without line breaks! This is very important, otherwise the regex will not find your ORDER.
This code sorts correctly and loads the rest of the list, when I scroll down, works with the ajax search and shows only the empty locations.
This is his code (I am using an image here, so you see, there is not much difference to the new code below, I also use SQL to make helping easier by copy&paste) again as sql:
Code: Select all
SELECT *
FROM (SELECT e1.ID_LocationNum,
e1.Location_Auto,
e1.ID_Lagerorte,
e1.ID_posX,
e1.ID_posY,
Min(e1.ID_posZ) AS id_posZ,
e1.Lagerort_Kurz
FROM (SELECT e.Location_Auto,
e.ID_Lagerorte,
e.ID_posX,
e.ID_posY,
e.ID_posZ,
e.ID_LocationNum,
e.ImmerZeigen,
ecomo_Lagerorte.Lagerort_Kurz
FROM ecomo_Location e
LEFT JOIN ecomo_Lagerorte
ON ecomo_Lagerorte.ID_LagerorteNum = e.ID_Lagerorte
HAVING ( e.ID_LocationNum NOT IN (SELECT ecomo_Container.ID_Location
FROM ecomo_Container) )
OR ( e.ImmerZeigen = 'Ja' )) e1
GROUP BY e1.ID_posX,
e1.ID_posY,
e1.Lagerort_Kurz) ecomo_Location
ORDER BY ecomo_Location.Lagerort_Kurz,
ecomo_Location.ID_posX,
ecomo_Location.ID_posY DESC,
ecomo_Location.id_posZ DESC
Now I changed my database to make locations not show if they are empty of ImmerZeigen=ja as above, but the user would be able to set a certain number that defines, when a location is full BelegtBei. A location will be shown in the dropdown list, if is has less entries in the table than the BelegtBei value is. This results in this SQL which works fine in phpmyadmin, returning the correct records in the correct order: again as sql:
Code: Select all
SELECT *
FROM (SELECT e1.ID_LocationNum,
e1.Location_Auto,
e1.ID_Lagerorte,
e1.ID_posX,
e1.ID_posY,
Min(e1.ID_posZ) AS id_posZ,
e1.Lagerort_Kurz
FROM (SELECT e.Location_Auto,
e.ID_Lagerorte,
e.ID_posX,
e.ID_posY,
e.ID_posZ,
e.ID_LocationNum,
e.ImmerZeigen,
ecomo_Lagerorte.Lagerort_Kurz
FROM ecomo_Location e
LEFT JOIN ecomo_Lagerorte
ON ecomo_Lagerorte.ID_LagerorteNum = e.ID_Lagerorte
HAVING ( e.ID_LocationNum IN (SELECT aLocation.ID_LocationNum
FROM ecomo_Location aLocation
INNER JOIN (SELECT e.ID_LocationNum,
Count(ecomo_Container.ID_ContainerNum) AS c
FROM ecomo_Location e
LEFT JOIN ecomo_Container
ON ecomo_Container.ID_Location = e.ID_LocationNum
GROUP BY e.ID_LocationNum) aZahlContVorhanden
ON aZahlContVorhanden.ID_LocationNum = aLocation.ID_LocationNum
WHERE aZahlContVorhanden.c < aLocation.BelegtBei) )) e1
GROUP BY e1.ID_posX,
e1.ID_posY,
e1.Lagerort_Kurz) ecomo_Location
ORDER BY ecomo_Location.Lagerort_Kurz,
ecomo_Location.ID_posX,
ecomo_Location.ID_posY DESC,
ecomo_Location.id_posZ DESC
As you see, I marked two sections with a border in the images. This is the only difference and the result of the complete SQL is exactly the same.
But with the new SQL the generated AppGini files do not work anymore!
The Order is still correct and loading more from the list works too, but AJAX search does not work - the spinner keeps spinning but does not suggest anything as can bee seen in the picture below. As the SQL does work in Mysql I am requesting your help. I can not see any reason, why this happens but I think something is strange with the AJAX search.
Olaf
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
Re: How? Sort lookup by different column
Hi,
in case you want to use the custom SQL, you need to make sure, that you do not use GROUP BY or ORDER BY within the query.
It only works at the end of the query. Otherwise the query will be broken, as discribed in the post.
in case you want to use the custom SQL, you need to make sure, that you do not use GROUP BY or ORDER BY within the query.
It only works at the end of the query. Otherwise the query will be broken, as discribed in the post.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
Re: How? Sort lookup by different column
Hi pbötcher,
thank you for that hint. I will try, try to reformulate my SQL and report back.
Probably you are correct - but if so, this is something I believe should be mentioned in the docs around page 15 in the "Understanding lookup fields" section.
Olaf
thank you for that hint. I will try, try to reformulate my SQL and report back.
Probably you are correct - but if so, this is something I believe should be mentioned in the docs around page 15 in the "Understanding lookup fields" section.
Olaf
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
Re: How? Sort lookup by different column
Hi pbötcher,
once again, your help is very much appreciated and was indeed correct. I solved it by taking
out of my query and created a view (view_FreiePlaetze) inside the database from that. I then replaced it in the query with
Works perfectly.
Olaf
once again, your help is very much appreciated and was indeed correct. I solved it by taking
Code: Select all
SELECT aLocation.ID_LocationNum
FROM ecomo_Location aLocation
INNER JOIN (SELECT e.ID_LocationNum,
Count(ecomo_Container.ID_ContainerNum) AS c
FROM ecomo_Location e
LEFT JOIN ecomo_Container
ON ecomo_Container.ID_Location = e.ID_LocationNum
GROUP BY e.ID_LocationNum) aZahlContVorhanden
ON aZahlContVorhanden.ID_LocationNum = aLocation.ID_LocationNum
WHERE aZahlContVorhanden.c < aLocation.BelegtBei
Code: Select all
SELECT ID_LocationNum FROM view_FreiePlaetze
Olaf
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view